<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="problems-with-alias">
      </a>
      B.3.4.4 Problems with Column Aliases
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053532384">
  </a>
  <a class="indexterm" name="idm46045053530928">
  </a>
  <a class="indexterm" name="idm46045053529440">
  </a>
  <p>
   An alias can be used in a query select list to give a column a
          different name. You can use the alias in
   <code class="literal">
    GROUP
          BY
   </code>
   ,
   <code class="literal">
    ORDER BY
   </code>
   , or
   <code class="literal">
    HAVING
   </code>
   clauses to refer to the column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83016483"><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">SQRT</span><span class="token punctuation">(</span>a<span class="token operator">*</span>b<span class="token punctuation">)</span> <span class="token keyword">AS</span> root <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> root <span class="token keyword">HAVING</span> root <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> cnt <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 keyword">HAVING</span> cnt <span class="token operator">&gt;</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> id <span class="token keyword">AS</span> <span class="token string">'Customer identity'</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Standard SQL disallows references to column aliases in a
   <code class="literal">
    WHERE
   </code>
   clause. This restriction is imposed
          because when the
   <code class="literal">
    WHERE
   </code>
   clause is evaluated,
          the column value may not yet have been determined. For
          example, the following query is illegal:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72728627"><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">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> cnt <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> cnt <span class="token operator">&gt;</span> <span class="token number">0</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> id<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    WHERE
   </code>
   clause determines which rows
          should be included in the
   <code class="literal">
    GROUP BY
   </code>
   clause,
          but it refers to the alias of a column value that is not known
          until after the rows have been selected, and grouped by the
   <code class="literal">
    GROUP BY
   </code>
   .
  </p>
  <p>
   In the select list of a query, a quoted column alias can be
          specified using identifier or string quoting characters:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39201726"><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 number">1</span> <span class="token keyword">AS</span> <span class="token punctuation">`</span><span class="token keyword">one</span><span class="token punctuation">`</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token keyword">AS</span> <span class="token string">'two'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Elsewhere in the statement, quoted references to the alias
          must use identifier quoting or the reference is treated as a
          string literal. For example, this statement groups by the
          values in column
   <code class="literal">
    id
   </code>
   , referenced using the
          alias
   <code class="literal">
    `a`
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51225262"><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 keyword">AS</span> <span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> cnt <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">`</span>a<span class="token punctuation">`</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This statement groups by the literal string
   <code class="literal">
    'a'
   </code>
   and does not work as you may expect:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92191554"><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 keyword">AS</span> <span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> cnt <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token string">'a'</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
