<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="group-by-handling">
      </a>
      14.19.3 MySQL Handling of GROUP BY
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045192598608">
  </a>
  <a class="indexterm" name="idm46045192597568">
  </a>
  <a class="indexterm" name="idm46045192596064">
  </a>
  <a class="indexterm" name="idm46045192594576">
  </a>
  <p>
   SQL-92 and earlier does not permit queries for which the select
        list,
   <code class="literal">
    HAVING
   </code>
   condition, or
   <code class="literal">
    ORDER
        BY
   </code>
   list refer to nonaggregated columns that are not
        named in the
   <code class="literal">
    GROUP BY
   </code>
   clause. For example,
        this query is illegal in standard SQL-92 because the
        nonaggregated
   <code class="literal">
    name
   </code>
   column in the select list
        does not appear in the
   <code class="literal">
    GROUP BY
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51138550"><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">SELECT</span> o<span class="token punctuation">.</span>custid<span class="token punctuation">,</span> c<span class="token punctuation">.</span><span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>o<span class="token punctuation">.</span>payment<span class="token punctuation">)</span>
  <span class="token keyword">FROM</span> orders <span class="token keyword">AS</span> o<span class="token punctuation">,</span> customers <span class="token keyword">AS</span> c
  <span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>custid <span class="token operator">=</span> c<span class="token punctuation">.</span>custid
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> o<span class="token punctuation">.</span>custid<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For the query to be legal in SQL-92, the
   <code class="literal">
    name
   </code>
   column must be omitted from the select list or named in the
   <code class="literal">
    GROUP BY
   </code>
   clause.
  </p>
  <p>
   SQL:1999 and later permits such nonaggregates per optional
        feature T301 if they are functionally dependent on
   <code class="literal">
    GROUP BY
   </code>
   columns: If such a relationship
        exists between
   <code class="literal">
    name
   </code>
   and
   <code class="literal">
    custid
   </code>
   , the query is legal. This would be the
        case, for example, were
   <code class="literal">
    custid
   </code>
   a primary key
        of
   <code class="literal">
    customers
   </code>
   .
  </p>
  <p>
   MySQL implements detection of functional dependence. If 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
        enabled (which it is by default), MySQL rejects queries for
        which the select list,
   <code class="literal">
    HAVING
   </code>
   condition, or
   <code class="literal">
    ORDER BY
   </code>
   list refer to nonaggregated columns
        that are neither named in the
   <code class="literal">
    GROUP BY
   </code>
   clause
        nor are functionally dependent on them.
  </p>
  <p>
   MySQL also permits a nonaggregate column not named in a
   <code class="literal">
    GROUP BY
   </code>
   clause when SQL
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   mode is
        enabled, provided that this column is limited to a single value,
        as shown in the following example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40495737"><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">CREATE</span> <span class="token keyword">TABLE</span> mytable <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>    id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    a <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    b <span class="token datatype">INT</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mytable
<span class="token prompt">    -&gt;</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token number">2000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'def'</span><span class="token punctuation">,</span> <span class="token number">4000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">SESSION</span> sql_mode <span class="token operator">=</span> sys<span class="token punctuation">.</span>list_add<span class="token punctuation">(</span><span class="token variable">@@session.sql_mode</span><span class="token punctuation">,</span> <span class="token string">'ONLY_FULL_GROUP_BY'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>b<span class="token punctuation">)</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">WHERE</span> a <span class="token operator">=</span> <span class="token string">'abc'</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> a    <span class="token punctuation">|</span> SUM(b) <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> abc  <span class="token punctuation">|</span>   3000 <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>
   It is also possible to have more than one nonaggregate column in
        the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   list when employing
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   . In this
        case, every such column must be limited to a single value in the
   <code class="literal">
    WHERE
   </code>
   clause, and all such limiting
        conditions must be joined by logical
   <code class="literal">
    AND
   </code>
   , as
        shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15963600"><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">DROP</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token keyword">EXISTS</span> mytable<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mytable <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>    id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    a <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    b <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    c <span class="token datatype">INT</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> mytable
<span class="token prompt">    -&gt;</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token string">'qrs'</span><span class="token punctuation">,</span> <span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token string">'tuv'</span><span class="token punctuation">,</span> <span class="token number">2000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'def'</span><span class="token punctuation">,</span> <span class="token string">'qrs'</span><span class="token punctuation">,</span> <span class="token number">4000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'def'</span><span class="token punctuation">,</span> <span class="token string">'tuv'</span><span class="token punctuation">,</span> <span class="token number">8000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token string">'qrs'</span><span class="token punctuation">,</span> <span class="token number">16000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>        <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token string">'def'</span><span class="token punctuation">,</span> <span class="token string">'tuv'</span><span class="token punctuation">,</span> <span class="token number">32000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@session.sql_mode</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>
<span class="token output"><span class="token punctuation">|</span> @@session.sql_mode                                            <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>
<span class="token output"><span class="token punctuation">|</span> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> a<span class="token punctuation">,</span> b<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>c<span class="token punctuation">)</span> <span class="token keyword">FROM</span> mytable
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> a <span class="token operator">=</span> <span class="token string">'abc'</span> <span class="token operator">AND</span> b <span class="token operator">=</span> <span class="token string">'qrs'</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>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b    <span class="token punctuation">|</span> SUM(c) <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>
<span class="token output"><span class="token punctuation">|</span> abc  <span class="token punctuation">|</span> qrs  <span class="token punctuation">|</span>  17000 <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></code></pre>
  </div>
  <p>
   If
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   is
        disabled, a MySQL extension to the standard SQL use of
   <code class="literal">
    GROUP BY
   </code>
   permits the select list,
   <code class="literal">
    HAVING
   </code>
   condition, or
   <code class="literal">
    ORDER
        BY
   </code>
   list to refer to nonaggregated columns even if the
        columns are not functionally dependent on
   <code class="literal">
    GROUP
        BY
   </code>
   columns. This causes MySQL to accept the preceding
        query. In this case, the server is free to choose any value from
        each group, so unless they are the same, the values chosen are
        nondeterministic, which is probably not what you want.
        Furthermore, the selection of values from each group cannot be
        influenced by adding an
   <code class="literal">
    ORDER BY
   </code>
   clause.
        Result set sorting occurs after values have been chosen, and
   <code class="literal">
    ORDER BY
   </code>
   does not affect which value within
        each group the server chooses. Disabling
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   is useful
        primarily when you know that, due to some property of the data,
        all values in each nonaggregated column not named in the
   <code class="literal">
    GROUP BY
   </code>
   are the same for each group.
  </p>
  <p>
   You can achieve the same effect without disabling
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   by using
   <a class="link" href="miscellaneous-functions.html#function_any-value">
    <code class="literal">
     ANY_VALUE()
    </code>
   </a>
   to refer to the
        nonaggregated column.
  </p>
  <p>
   The following discussion demonstrates functional dependence, the
        error message MySQL produces when functional dependence is
        absent, and ways of causing MySQL to accept a query in the
        absence of functional dependence.
  </p>
  <p>
   This query might be invalid with
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   enabled
        because the nonaggregated
   <code class="literal">
    address
   </code>
   column in
        the select list is not named in the
   <code class="literal">
    GROUP BY
   </code>
   clause:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10195796"><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">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> address<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The query is valid if
   <code class="literal">
    name
   </code>
   is a primary key
        of
   <code class="literal">
    t
   </code>
   or is a unique
   <code class="literal">
    NOT
        NULL
   </code>
   column. In such cases, MySQL recognizes that the
        selected column is functionally dependent on a grouping column.
        For example, if
   <code class="literal">
    name
   </code>
   is a primary key, its
        value determines the value of
   <code class="literal">
    address
   </code>
   because
        each group has only one value of the primary key and thus only
        one row. As a result, there is no randomness in the choice of
   <code class="literal">
    address
   </code>
   value in a group and no need to
        reject the query.
  </p>
  <p>
   The query is invalid if
   <code class="literal">
    name
   </code>
   is not a primary
        key of
   <code class="literal">
    t
   </code>
   or a unique
   <code class="literal">
    NOT
        NULL
   </code>
   column. In this case, no functional dependency
        can be inferred and an error occurs:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69731397"><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> address<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1055 (42000)<span class="token punctuation">:</span> Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by</span></code></pre>
  </div>
  <p>
   If you know that,
   <span class="emphasis">
    <em>
     for a given data set,
    </em>
   </span>
   each
   <code class="literal">
    name
   </code>
   value in fact uniquely determines
        the
   <code class="literal">
    address
   </code>
   value,
   <code class="literal">
    address
   </code>
   is effectively functionally dependent on
   <code class="literal">
    name
   </code>
   . To tell MySQL to accept the query, you
        can use the
   <a class="link" href="miscellaneous-functions.html#function_any-value">
    <code class="literal">
     ANY_VALUE()
    </code>
   </a>
   function:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa98851196"><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">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">ANY_VALUE</span><span class="token punctuation">(</span>address<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Alternatively, disable
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   .
  </p>
  <p>
   The preceding example is quite simple, however. In particular,
        it is unlikely you would group on a single primary key column
        because every group would contain only one row. For additional
        examples demonstrating functional dependence in more complex
        queries, see
   <a class="xref" href="group-by-functional-dependence.html" title="14.19.4 Detection of Functional Dependence">
    Section 14.19.4, “Detection of Functional Dependence”
   </a>
   .
  </p>
  <p>
   If a query has aggregate functions and no
   <code class="literal">
    GROUP
        BY
   </code>
   clause, it cannot have nonaggregated columns in the
        select list,
   <code class="literal">
    HAVING
   </code>
   condition, or
   <code class="literal">
    ORDER BY
   </code>
   list with
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   enabled:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79302699"><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> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
<span class="token output">ERROR 1140 (42000)<span class="token punctuation">:</span> In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by</span></code></pre>
  </div>
  <p>
   Without
   <code class="literal">
    GROUP BY
   </code>
   , there is a single group and
        it is nondeterministic which
   <code class="literal">
    name
   </code>
   value to
        choose for the group. Here, too,
   <a class="link" href="miscellaneous-functions.html#function_any-value">
    <code class="literal">
     ANY_VALUE()
    </code>
   </a>
   can be used, if it is
        immaterial which
   <code class="literal">
    name
   </code>
   value MySQL chooses:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63926716"><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">SELECT</span> <span class="token function">ANY_VALUE</span><span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>age<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    ONLY_FULL_GROUP_BY
   </code>
   also affects handling of
        queries that use
   <code class="literal">
    DISTINCT
   </code>
   and
   <code class="literal">
    ORDER
        BY
   </code>
   . Consider the case of a table
   <code class="literal">
    t
   </code>
   with three columns
   <code class="literal">
    c1
   </code>
   ,
   <code class="literal">
    c2
   </code>
   ,
        and
   <code class="literal">
    c3
   </code>
   that contains these rows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa12201721"><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-none">c1 c2 c3
1  2  A
3  4  B
1  2  C</code></pre>
  </div>
  <p>
   Suppose that we execute the following query, expecting the
        results to be ordered by
   <code class="literal">
    c3
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47190664"><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">SELECT</span> <span class="token keyword">DISTINCT</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> c3<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To order the result, duplicates must be eliminated first. But to
        do so, should we keep the first row or the third? This arbitrary
        choice influences the retained value of
   <code class="literal">
    c3
   </code>
   ,
        which in turn influences ordering and makes it arbitrary as
        well. To prevent this problem, a query that has
   <code class="literal">
    DISTINCT
   </code>
   and
   <code class="literal">
    ORDER BY
   </code>
   is
        rejected as invalid if any
   <code class="literal">
    ORDER BY
   </code>
   expression does not satisfy at least one of these conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The expression is equal to one in the select list
     </p>
    </li>
    <li class="listitem">
     <p>
      All columns referenced by the expression and belonging to
            the query's selected tables are elements of the select list
     </p>
    </li>
   </ul>
  </div>
  <p>
   Another MySQL extension to standard SQL permits references in
        the
   <code class="literal">
    HAVING
   </code>
   clause to aliased expressions in
        the select list. For example, the following query returns
   <code class="literal">
    name
   </code>
   values that occur only once in table
   <code class="literal">
    orders
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57849073"><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">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> orders
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span>
  <span class="token keyword">HAVING</span> <span class="token function">COUNT</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></code></pre>
  </div>
  <p>
   The MySQL extension permits the use of an alias in the
   <code class="literal">
    HAVING
   </code>
   clause for the aggregated column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90783597"><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">SELECT</span> <span class="token keyword">name</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> c <span class="token keyword">FROM</span> orders
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">name</span>
  <span class="token keyword">HAVING</span> c <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <a class="indexterm" name="idm46045192480544">
  </a>
  <a class="indexterm" name="idm46045192479056">
  </a>
  <a class="indexterm" name="idm46045192477568">
  </a>
  <a class="indexterm" name="idm46045192476496">
  </a>
  <p>
   Standard SQL permits only column expressions in
   <code class="literal">
    GROUP
        BY
   </code>
   clauses, so a statement such as this is invalid
        because
   <code class="literal">
    FLOOR(value/100)
   </code>
   is a noncolumn
        expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72123799"><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">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL extends standard SQL to permit noncolumn expressions in
   <code class="literal">
    GROUP BY
   </code>
   clauses and considers the preceding
        statement valid.
  </p>
  <p>
   Standard SQL also does not permit aliases in
   <code class="literal">
    GROUP
        BY
   </code>
   clauses. MySQL extends standard SQL to permit
        aliases, so another way to write the query is as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84524328"><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">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> val
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> id<span class="token punctuation">,</span> val<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The alias
   <code class="literal">
    val
   </code>
   is considered a column
        expression in the
   <code class="literal">
    GROUP BY
   </code>
   clause.
  </p>
  <p>
   In the presence of a noncolumn expression in the
   <code class="literal">
    GROUP
        BY
   </code>
   clause, MySQL recognizes equality between that
        expression and expressions in the select list. This means that
        with
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   SQL
        mode enabled, the query containing
   <code class="literal">
    GROUP BY id,
        FLOOR(value/100)
   </code>
   is valid because that same
   <a class="link" href="mathematical-functions.html#function_floor">
    <code class="literal">
     FLOOR()
    </code>
   </a>
   expression occurs in the
        select list. However, MySQL does not try to recognize functional
        dependence on
   <code class="literal">
    GROUP BY
   </code>
   noncolumn expressions,
        so the following query is invalid with
   <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
    <code class="literal">
     ONLY_FULL_GROUP_BY
    </code>
   </a>
   enabled,
        even though the third selected expression is a simple formula of
        the
   <code class="literal">
    id
   </code>
   column and the
   <a class="link" href="mathematical-functions.html#function_floor">
    <code class="literal">
     FLOOR()
    </code>
   </a>
   expression in the
   <code class="literal">
    GROUP BY
   </code>
   clause:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48038222"><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">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span> id<span class="token operator">+</span><span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A workaround is to use a derived table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1858445"><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">SELECT</span> id<span class="token punctuation">,</span> F<span class="token punctuation">,</span> id<span class="token operator">+</span>F
  <span class="token keyword">FROM</span>
    <span class="token punctuation">(</span><span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> F
     <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
     <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> id<span class="token punctuation">,</span> <span class="token function">FLOOR</span><span class="token punctuation">(</span><span class="token keyword">value</span><span class="token operator">/</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> dt<span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
