title: How to fix 'must be an aggregate expression or appear in GROUP BY clause' with 'as' clause
body: <p>I am trying to get the number of request by hour of my CloudFront distribution using athena query. </p>
  
  <p>I created cloudfront_logs table guided by <a href="https://docs.aws.amazon.com/ko_kr/athena/latest/ug/cloudfront-logs.html" rel="nofollow noreferrer">this Link</a> in my sample_db </p>
  
  <p>Below is the query I made to get the number of request by hour </p>
  
  <pre><code>SELECT date_trunc('hour',from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time))) as TIME, count(*) as CNT
  FROM "sample_db"."cloudfront_logs" 
  WHERE 
      from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)) &gt;= from_iso8601_timestamp('2019-05-29T00:00:00')
  AND 
      from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)) &lt; from_iso8601_timestamp('2019-05-30T00:00:00')
  GROUP BY TIME
  ORDER BY TIME ASC;
  </code></pre>
  
  <p>However it returns error like this </p>
  
  <pre><code>SYNTAX_ERROR: line 2:8: '"date_trunc"('hour', "from_iso8601_timestamp"("concat"("concat"("date_format"("date", '%Y-%m-%d'), 'T'), "time")))' must be an aggregate expression or appear in GROUP BY clause
  </code></pre>
  
  <p>Therefore, I replaced the <code>TIME</code> after <code>GROUP BY</code> to <code>date_trunc('hour',from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)))</code>, and tried again. </p>
  
  <pre><code>SELECT date_trunc('hour',from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time))) as TIME, count(*) as CNT
  FROM "sample_db"."cloudfront_logs" 
  WHERE 
      from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)) &gt;= from_iso8601_timestamp('2019-05-29T00:00:00')
  AND 
      from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)) &lt; from_iso8601_timestamp('2019-05-30T00:00:00')
  GROUP BY date_trunc('hour',from_iso8601_timestamp(concat(concat(date_format(date, '%Y-%m-%d'), 'T'), time)))
  ORDER BY TIME ASC;
  </code></pre>
  
  <p>Finally, I got result. </p>
  
  <p>I think, it should work with the first query also. Can I get any advice on the first query? Because It looks more simple.</p>