Where is a non-aggregate value that was not included in a group?

Question

I get this error message, but I cannot see where exactly is the problem:

Error Code - 3504 Error Message - [Teradata Database] [TeraJDBC 16.20.00.12] [Error 3504] [SQLState HY000] Selected non-aggregate values must be part of the associated group.

SELECT EXTRACT(MONTH FROM trnsact.saledate)||EXTRACT(YEAR FROM trnsact.saledate) AS MonthYear, 
       SUM(trnsact.amt)/COUNT(DISTINCT trnsact.saledate) AS AvgSales
FROM (SELECT EXTRACT(MONTH FROM saledate)||EXTRACT(YEAR FROM saledate) AS MonthYear, store
      FROM trnsact
      HAVING COUNT(DISTINCT SALEDATE)>19
      GROUP BY MonthYear, store) AS cleaned 
JOIN trnsact ON cleaned.MonthYear=MonthYear AND cleaned.store=trnsact.store 
WHERE STYPE='P' AND saledate < '2005-08-01' 
GROUP BY MonthYear
ORDER BY AvgSales;

SQL doesn't allow using alias names from the same query in the HAVING clause, because the HAVING clause gets executed before the SELECT clause, so the alias defined in the SELECT clause is not known to the HAVING clause.. — Thorsten Kettner
@ThorstenKettner: Standard SQL doesn't allow it, but Teradata SQL :-) — dnoeth
I'd avoid reusing MonthYear anyway. Too confusing as it is now. — jarlh

dnoeth dnoeth · Accepted Answer · 2020-08-25T21:49:41

Teradata allows reusing an alias in any place, but the are some scoping rules and the parser is probably confused because there're two monthyear columns, an alias of the outer Select and a column name form the inner Select. Using two different names should fix that. Additionally it will be more efficient to use separate columns for year/month, e.g.:

SELECT EXTRACT(MONTH FROM trnsact.saledate) as mon,
  EXTRACT(YEAR FROM trnsact.saledate) AS yr, 
  SUM(trnsact.amt)/COUNT(DISTINCT trnsact.saledate) AS AvgSales

FROM  
 (
   SELECT EXTRACT(MONTH FROM saledate) as mon2,
      EXTRACT(YEAR FROM saledate) AS yr2, store
   FROM trnsact
   -- fixed the order, Teradata allows that wrong order, but it's really confusing
   GROUP BY mon2, yr2, store
   HAVING COUNT(DISTINCT SALEDATE)>19
 ) AS cleaned 
 
JOIN trnsact 
ON cleaned.mon2=mon
AND cleaned.yr2=yr 
AND cleaned.store=trnsact.store 

WHERE STYPE='P'  
  AND saledate < DATE '2005-08-01' -- using a date literal is safe

GROUP BY yr,mon

ORDER BY AvgSales;

Edit:

This can probably be simplified to:

SELECT
   EXTRACT(YEAR FROM saledate) AS yr
  ,EXTRACT(MONTH FROM saledate) AS mon
   -- = SUM(trnsact.amt)/COUNT(DISTINCT trnsact.saledate)
  ,SUM(sumamt)/COUNT(DISTINCT saledate) AS AvgSales
FROM
 (
   SELECT saledate, store, SUM(amt) AS sumamt
   FROM sales_fact
   WHERE STYPE='P'  
     AND saledate < DATE '2005-08-01' -- using a date literal is safe
   GROUP BY 1, 2
   -- = HAVING COUNT(DISTINCT SALEDATE) > 19
   QUALIFY COUNT(*) OVER (PARTITION BY store_id) > 19
 ) AS cleaned
GROUP BY 1, 2
ORDER BY AvgSales;

Where is a non-aggregate value that was not included in a group?

1 Answers