<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="group-by-modifiers">
      </a>
      14.19.2 GROUP BY Modifiers
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045192776816">
  </a>
  <a class="indexterm" name="idm46045192775744">
  </a>
  <a class="indexterm" name="idm46045192774672">
  </a>
  <a class="indexterm" name="idm46045192773600">
  </a>
  <a class="indexterm" name="idm46045192772112">
  </a>
  <p>
   The
   <code class="literal">
    GROUP BY
   </code>
   clause permits a
   <code class="literal">
    WITH
        ROLLUP
   </code>
   modifier that causes summary output to include
        extra rows that represent higher-level (that is,
        super-aggregate) summary operations.
   <code class="literal">
    ROLLUP
   </code>
   thus enables you to answer questions at multiple levels of
        analysis with a single query. For example,
   <code class="literal">
    ROLLUP
   </code>
   can be used to provide support for
        OLAP (Online Analytical Processing) operations.
  </p>
  <p>
   Suppose that a
   <code class="literal">
    sales
   </code>
   table has
   <code class="literal">
    year
   </code>
   ,
   <code class="literal">
    country
   </code>
   ,
   <code class="literal">
    product
   </code>
   , and
   <code class="literal">
    profit
   </code>
   columns for recording sales profitability:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43138516"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> sales
<span class="token punctuation">(</span>
    <span class="token datatype">year</span>    <span class="token datatype">INT</span><span class="token punctuation">,</span>
    country <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    product <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    profit  <span class="token datatype">INT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To summarize table contents per year, use a simple
   <code class="literal">
    GROUP BY
   </code>
   like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86382169"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The output shows the total (aggregate) profit for each year. To
        also determine the total profit summed over all years, you must
        add up the individual values yourself or run an additional
        query. Or you can use
   <code class="literal">
    ROLLUP
   </code>
   , which provides
        both levels of analysis with a single query. Adding a
   <code class="literal">
    WITH ROLLUP
   </code>
   modifier to the
   <code class="literal">
    GROUP
        BY
   </code>
   clause causes the query to produce another
        (super-aggregate) row that shows the grand total over all year
        values:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15470369"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    NULL
   </code>
   value in the
   <code class="literal">
    year
   </code>
   column identifies the grand total super-aggregate line.
  </p>
  <p>
   <code class="literal">
    ROLLUP
   </code>
   has a more complex effect when there
        are multiple
   <code class="literal">
    GROUP BY
   </code>
   columns. In this case,
        each time there is a change in value in any but the last
        grouping column, the query produces an extra super-aggregate
        summary row.
  </p>
  <p>
   For example, without
   <code class="literal">
    ROLLUP
   </code>
   , a summary of the
   <code class="literal">
    sales
   </code>
   table based on
   <code class="literal">
    year
   </code>
   ,
   <code class="literal">
    country
   </code>
   , and
   <code class="literal">
    product
   </code>
   might
        look like this, where the output indicates summary values only
        at the year/country/product level of analysis:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20019911"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   2700 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    250 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   With
   <code class="literal">
    ROLLUP
   </code>
   added, the query produces several
        extra rows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67001293"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1600 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   2700 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    250 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   3000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Now the output includes summary information at four levels of
        analysis, not just one:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Following each set of product rows for a given year and
            country, an extra super-aggregate summary row appears
            showing the total for all products. These rows have the
      <code class="literal">
       product
      </code>
      column set to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Following each set of rows for a given year, an extra
            super-aggregate summary row appears showing the total for
            all countries and products. These rows have the
      <code class="literal">
       country
      </code>
      and
      <code class="literal">
       products
      </code>
      columns set to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Finally, following all other rows, an extra super-aggregate
            summary row appears showing the grand total for all years,
            countries, and products. This row has the
      <code class="literal">
       year
      </code>
      ,
      <code class="literal">
       country
      </code>
      , and
      <code class="literal">
       products
      </code>
      columns set to
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    NULL
   </code>
   indicators in each super-aggregate
        row are produced when the row is sent to the client. The server
        looks at the columns named in the
   <code class="literal">
    GROUP BY
   </code>
   clause following the leftmost one that has changed value. For
        any column in the result set with a name that matches any of
        those names, its value is set to
   <code class="literal">
    NULL
   </code>
   . (If
        you specify grouping columns by column position, the server
        identifies which columns to set to
   <code class="literal">
    NULL
   </code>
   by
        position.)
  </p>
  <p>
   Because the
   <code class="literal">
    NULL
   </code>
   values in the
        super-aggregate rows are placed into the result set at such a
        late stage in query processing, you can test them as
   <code class="literal">
    NULL
   </code>
   values only in the select list or
   <code class="literal">
    HAVING
   </code>
   clause. You cannot test them as
   <code class="literal">
    NULL
   </code>
   values in join conditions or the
   <code class="literal">
    WHERE
   </code>
   clause to determine which rows to
        select. For example, you cannot add
   <code class="literal">
    WHERE product IS
        NULL
   </code>
   to the query to eliminate from the output all but
        the super-aggregate rows.
  </p>
  <p>
   The
   <code class="literal">
    NULL
   </code>
   values do appear as
   <code class="literal">
    NULL
   </code>
   on the client side and can be tested as
        such using any MySQL client programming interface. However, at
        this point, you cannot distinguish whether a
   <code class="literal">
    NULL
   </code>
   represents a regular grouped value or a
        super-aggregate value. To test the distinction, use the
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   function, described
        later.
  </p>
  <p>
   For
   <code class="literal">
    GROUP BY ... WITH ROLLUP
   </code>
   queries, to test
        whether
   <code class="literal">
    NULL
   </code>
   values in the result represent
        super-aggregate values, the
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   function is available
        for use in the select list,
   <code class="literal">
    HAVING
   </code>
   clause,
        and
   <code class="literal">
    ORDER BY
   </code>
   clause. For example,
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING(year)
    </code>
   </a>
   returns 1 when
   <code class="literal">
    NULL
   </code>
   in the
   <code class="literal">
    year
   </code>
   column
        occurs in a super-aggregate row, and 0 otherwise. Similarly,
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING(country)
    </code>
   </a>
   and
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING(product)
    </code>
   </a>
   return 1 for
        super-aggregate
   <code class="literal">
    NULL
   </code>
   values in the
   <code class="literal">
    country
   </code>
   and
   <code class="literal">
    product
   </code>
   columns, respectively:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73998215"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit<span class="token punctuation">,</span>
         <span class="token function">GROUPING</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> grp_year<span class="token punctuation">,</span>
         <span class="token function">GROUPING</span><span class="token punctuation">(</span>country<span class="token punctuation">)</span> <span class="token keyword">AS</span> grp_country<span class="token punctuation">,</span>
         <span class="token function">GROUPING</span><span class="token punctuation">(</span>product<span class="token punctuation">)</span> <span class="token keyword">AS</span> grp_product
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span> grp_year <span class="token punctuation">|</span> grp_country <span class="token punctuation">|</span> grp_product <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1600 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1350 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1575 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   2700 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> TV         <span class="token punctuation">|</span>    250 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   3000 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span>        1 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span>           1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Instead of displaying the
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   results directly, you
        can use
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   to substitute
        labels for super-aggregate
   <code class="literal">
    NULL
   </code>
   values:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99360513"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">GROUPING</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'All years'</span><span class="token punctuation">,</span> <span class="token datatype">year</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token datatype">year</span><span class="token punctuation">,</span>
         <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">GROUPING</span><span class="token punctuation">(</span>country<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'All countries'</span><span class="token punctuation">,</span> country<span class="token punctuation">)</span> <span class="token keyword">AS</span> country<span class="token punctuation">,</span>
         <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">GROUPING</span><span class="token punctuation">(</span>product<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'All products'</span><span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token keyword">AS</span> product<span class="token punctuation">,</span>
         <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year      <span class="token punctuation">|</span> country       <span class="token punctuation">|</span> product      <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> Finland       <span class="token punctuation">|</span> Computer     <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> Finland       <span class="token punctuation">|</span> Phone        <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> Finland       <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   1600 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> India         <span class="token punctuation">|</span> Calculator   <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> India         <span class="token punctuation">|</span> Computer     <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> India         <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> Calculator   <span class="token punctuation">|</span>     75 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> Computer     <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   1575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000      <span class="token punctuation">|</span> All countries <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> Finland       <span class="token punctuation">|</span> Phone        <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> Finland       <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> Calculator   <span class="token punctuation">|</span>     50 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> Computer     <span class="token punctuation">|</span>   2700 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> TV           <span class="token punctuation">|</span>    250 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> USA           <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   3000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001      <span class="token punctuation">|</span> All countries <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> All years <span class="token punctuation">|</span> All countries <span class="token punctuation">|</span> All products <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   With multiple expression arguments,
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   returns a result
        representing a bitmask that combines the results for each
        expression, with the lowest-order bit corresponding to the
        result for the rightmost expression. For example,
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING(year, country, product)
    </code>
   </a>
   is evaluated like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa2509228"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-clike">  result <span class="token keyword">for</span> <span class="token function">GROUPING</span><span class="token punctuation">(</span><em class="replaceable">product</em><span class="token punctuation">)</span>
<span class="token operator">+</span> result <span class="token keyword">for</span> <span class="token function">GROUPING</span><span class="token punctuation">(</span><em class="replaceable">country</em><span class="token punctuation">)</span> <span class="token operator">&lt;</span><span class="token operator">&lt;</span> <span class="token number">1</span>
<span class="token operator">+</span> result <span class="token keyword">for</span> <span class="token function">GROUPING</span><span class="token punctuation">(</span><em class="replaceable">year</em><span class="token punctuation">)</span> <span class="token operator">&lt;</span><span class="token operator">&lt;</span> <span class="token number">2</span></code></pre>
  </div>
  <p>
   The result of such a
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   is nonzero if any of the expressions represents a
        super-aggregate
   <code class="literal">
    NULL
   </code>
   , so you can return only
        the super-aggregate rows and filter out the regular grouped rows
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69606547"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span>
       <span class="token keyword">HAVING</span> <span class="token function">GROUPING</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token operator">&lt;&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   1600 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   1350 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   1575 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>     10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   3000 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    sales
   </code>
   table contains no
   <code class="literal">
    NULL
   </code>
   values, so all
   <code class="literal">
    NULL
   </code>
   values in a
   <code class="literal">
    ROLLUP
   </code>
   result represent
        super-aggregate values. When the data set contains
   <code class="literal">
    NULL
   </code>
   values,
   <code class="literal">
    ROLLUP
   </code>
   summaries may contain
   <code class="literal">
    NULL
   </code>
   values not only in
        super-aggregate rows, but also in regular grouped rows.
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   enables these to be
        distinguished. Suppose that table
   <code class="literal">
    t1
   </code>
   contains
        a simple data set with two grouping factors for a set of
        quantity values, where
   <code class="literal">
    NULL
   </code>
   indicates
        something like
   <span class="quote">
    “
    <span class="quote">
     other
    </span>
    ”
   </span>
   or
   <span class="quote">
    “
    <span class="quote">
     unknown
    </span>
    ”
   </span>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7113465"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name <span class="token punctuation">|</span> size  <span class="token punctuation">|</span> quantity <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> small <span class="token punctuation">|</span>       10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> large <span class="token punctuation">|</span>       20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> small <span class="token punctuation">|</span>       15 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> large <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   A simple
   <code class="literal">
    ROLLUP
   </code>
   operation produces these
        results, in which it is not so easy to distinguish
   <code class="literal">
    NULL
   </code>
   values in super-aggregate rows from
   <code class="literal">
    NULL
   </code>
   values in regular grouped rows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86556644"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> size<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token keyword">AS</span> quantity
       <span class="token keyword">FROM</span> t1
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">,</span> size <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name <span class="token punctuation">|</span> size  <span class="token punctuation">|</span> quantity <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> large <span class="token punctuation">|</span>       20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> small <span class="token punctuation">|</span>       10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>       35 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> large <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> small <span class="token punctuation">|</span>       15 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>       23 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL  <span class="token punctuation">|</span>       58 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   Using
   <a class="link" href="miscellaneous-functions.html#function_grouping">
    <code class="literal">
     GROUPING()
    </code>
   </a>
   to substitute
        labels for the super-aggregate
   <code class="literal">
    NULL
   </code>
   values
        makes the result easier to interpret:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89217868"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">GROUPING</span><span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'All items'</span><span class="token punctuation">,</span> <span class="token keyword">name</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">name</span><span class="token punctuation">,</span>
         <span class="token function">IF</span><span class="token punctuation">(</span><span class="token function">GROUPING</span><span class="token punctuation">(</span>size<span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'All sizes'</span><span class="token punctuation">,</span> size<span class="token punctuation">)</span> <span class="token keyword">AS</span> size<span class="token punctuation">,</span>
         <span class="token function">SUM</span><span class="token punctuation">(</span>quantity<span class="token punctuation">)</span> <span class="token keyword">AS</span> quantity
       <span class="token keyword">FROM</span> t1
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">,</span> size <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name      <span class="token punctuation">|</span> size      <span class="token punctuation">|</span> quantity <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> ball      <span class="token punctuation">|</span> NULL      <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball      <span class="token punctuation">|</span> large     <span class="token punctuation">|</span>       20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball      <span class="token punctuation">|</span> small     <span class="token punctuation">|</span>       10 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ball      <span class="token punctuation">|</span> All sizes <span class="token punctuation">|</span>       35 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop      <span class="token punctuation">|</span> NULL      <span class="token punctuation">|</span>        3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop      <span class="token punctuation">|</span> large     <span class="token punctuation">|</span>        5 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop      <span class="token punctuation">|</span> small     <span class="token punctuation">|</span>       15 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> hoop      <span class="token punctuation">|</span> All sizes <span class="token punctuation">|</span>       23 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> All items <span class="token punctuation">|</span> All sizes <span class="token punctuation">|</span>       58 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="group-by-modifiers-other-considerations-when-using-rollup">
       </a>
       Other Considerations When using ROLLUP
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following discussion lists some behaviors specific to the
          MySQL implementation of
    <code class="literal">
     ROLLUP
    </code>
    .
   </p>
   <p>
    <code class="literal">
     ORDER BY
    </code>
    and
    <code class="literal">
     ROLLUP
    </code>
    can
          be used together, which enables the use of
    <code class="literal">
     ORDER
          BY
    </code>
    and
    <a class="link" href="miscellaneous-functions.html#function_grouping">
     <code class="literal">
      GROUPING()
     </code>
    </a>
    to
          achieve a specific sort order of grouped results. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5297568"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span>
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">GROUPING</span><span class="token punctuation">(</span><span class="token datatype">year</span><span class="token punctuation">)</span> <span class="token keyword">DESC</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    In both cases, the super-aggregate summary rows sort with the
          rows from which they are calculated, and their placement
          depends on sort order (at the end for ascending sort, at the
          beginning for descending sort).
   </p>
   <p>
    <code class="literal">
     LIMIT
    </code>
    can be used to restrict the number of
          rows returned to the client.
    <code class="literal">
     LIMIT
    </code>
    is
          applied after
    <code class="literal">
     ROLLUP
    </code>
    , so the limit applies
          against the extra rows added by
    <code class="literal">
     ROLLUP
    </code>
    . For
          example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68469933"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> product <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span>
       <span class="token keyword">LIMIT</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> product    <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1500 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> Phone      <span class="token punctuation">|</span>    100 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> Finland <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span>   1600 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Calculator <span class="token punctuation">|</span>    150 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span> Computer   <span class="token punctuation">|</span>   1200 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    Using
    <code class="literal">
     LIMIT
    </code>
    with
    <code class="literal">
     ROLLUP
    </code>
    may produce results that are more difficult to interpret,
          because there is less context for understanding the
          super-aggregate rows.
   </p>
   <p>
    A MySQL extension permits a column that does not appear in the
    <code class="literal">
     GROUP BY
    </code>
    list to be named in the select
          list. (For information about nonaggregated columns and
    <code class="literal">
     GROUP BY
    </code>
    , see
    <a class="xref" href="group-by-handling.html" title="14.19.3 MySQL Handling of GROUP BY">
     Section 14.19.3, “MySQL Handling of GROUP BY”
    </a>
    .) In this case, the server
          is free to choose any value from this nonaggregated column in
          summary rows, and this includes the extra rows added by
    <code class="literal">
     WITH ROLLUP
    </code>
    . For example, in the following
          query,
    <code class="literal">
     country
    </code>
    is a nonaggregated column
          that does not appear in the
    <code class="literal">
     GROUP BY
    </code>
    list
          and values chosen for this column are nondeterministic:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68023766"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> country<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    This behavior is permitted when the
    <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
     <code class="literal">
      ONLY_FULL_GROUP_BY
     </code>
    </a>
    SQL mode
          is not enabled. If that mode is enabled, the server rejects
          the query as illegal because
    <code class="literal">
     country
    </code>
    is not
          listed in the
    <code class="literal">
     GROUP BY
    </code>
    clause. With
    <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
     <code class="literal">
      ONLY_FULL_GROUP_BY
     </code>
    </a>
    enabled,
          you can still execute the query by using the
    <a class="link" href="miscellaneous-functions.html#function_any-value">
     <code class="literal">
      ANY_VALUE()
     </code>
    </a>
    function for
          nondeterministic-value columns:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22868350"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token datatype">year</span><span class="token punctuation">,</span> <span class="token function">ANY_VALUE</span><span class="token punctuation">(</span>country<span class="token punctuation">)</span> <span class="token keyword">AS</span> country<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>profit<span class="token punctuation">)</span> <span class="token keyword">AS</span> profit
       <span class="token keyword">FROM</span> sales
       <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token datatype">year</span> <span class="token keyword">WITH</span> <span class="token keyword">ROLLUP</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> year <span class="token punctuation">|</span> country <span class="token punctuation">|</span> profit <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2000 <span class="token punctuation">|</span> India   <span class="token punctuation">|</span>   4525 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2001 <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span>   3010 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> USA     <span class="token punctuation">|</span>   7535 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    A rollup column cannot be used as an argument to
    <a class="link" href="fulltext-search.html#function_match">
     <code class="literal">
      MATCH()
     </code>
    </a>
    (and is rejected with
          an error) except when called in a
    <code class="literal">
     WHERE
    </code>
    clause. See
    <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
     Section 14.9, “Full-Text Search Functions”
    </a>
    , for more
          information.
   </p>
  </div>
 </div>
 <br/>
</div>
