title: Do I need to state everything in the derived table in Teradata
body: <p>I have encounter some confuses when writing queries:</p>
  
  <ol>
  <li><p>Do I need to state everything in the subquery so as to use in the outermost query?</p></li>
  <li><p>I am calculating the average revenue for each high school graduation rate(msa_high) group, but teradata keeps informing " Selected non-aggregate values must be part of the associated group."</p></li>
  </ol>
  
  <p>Appreciate any helps, thanks a lot !</p>
  
  <pre><code>SELECT CASE 
  WHEN subquery.msa_high&gt;=50 AND subquery.msa_high&lt;=60 THEN 'low'
  WHEN subquery.msa_high&gt;=60.1 AND subquery.msa_high &lt;=70 THEN 'medium'
  WHEN subquery.msa_high&gt;70 THEN 'high'
  END AS rate,
  SUM(subquery.amt)/COUNT(DISTINCT subquery.saledate) as AvgRevenue
  FROM (SELECT s.store, SUM(t.amt)/COUNT(DISTINCT t.saledate), s.msa_high
        FROM trnsact t JOIN store_msa s
        ON t.store=s.store
        WHERE t.stype='P' 
        AND (t.saledate NOT BETWEEN DATE '2005-08-01' AND LAST_DAY(DATE '2005-08-27'))
        HAVING COUNT(DISTINCT t.saledate)&gt;20) as subquery
  GROUP BY rate;
  </code></pre>
  
  <p>The accurate query is as followed, thanks to the helps:</p>
  
  <pre><code>SELECT CASE 
  WHEN subquery.msa_high&gt;=50 AND subquery.msa_high&lt;=60 THEN 'low'
  WHEN subquery.msa_high&gt;=60.1 AND subquery.msa_high &lt;=70 THEN 'medium'
  WHEN subquery.msa_high&gt;70 THEN 'high'
  END AS rate,
  SUM(subquery.amt)/SUM(subquery.saledate) as AvgRevenue
  FROM (SELECT s.store, s.msa_high, SUM(t.amt) as amt, COUNT(DISTINCT t.saledate) as saledate
        FROM trnsact t JOIN store_msa s
        ON t.store=s.store
        WHERE t.stype='P' 
        AND (t.saledate NOT BETWEEN DATE '2005-08-01' AND LAST_DAY(DATE '2005-08-27'))
        GROUP BY s.store, s.msa_high
        HAVING COUNT(DISTINCT t.saledate)&gt;20) as subquery
  GROUP BY rate;
  </code></pre>