Error with group by statement?

Question

I'm getting the following error when I run this code. please help me out.

SELECT store, COUNT(DISTINCT saledate), CountNov, CountDec, SumNov, SumDec, (SumNov/CountNov) AS NovAvgRvn, (SumDec/CountDec) AS DecAvgRvn FROM 
(
SELECT store, saledate, 
CASE WHEN SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN amt END) IS NULL THEN 0 
ELSE SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN amt END)
END AS SumNov, 
CASE WHEN SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN amt END) IS NULL THEN 0 
ELSE SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN amt END)
END AS SumDec, 
CASE WHEN COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN saledate END) IS NULL THEN 0 
ELSE COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN saledate END)
END AS CountNov, 
CASE WHEN COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN saledate END) IS NULL THEN 0 
ELSE COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN saledate END)
END AS CountDec 
FROM trnsact 
WHERE stype = 'p' 
GROUP BY store, saledate  
) AS T1 
WHERE CountDec > 0 AND CountNov > 0 
GROUP BY store 
ORDER BY store;

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

Answer 1

Anything that is in your select statement which is not an aggregate like SUM, AVG etc. need to be included in your group by clause if you have a group by clause

Try this hope it helps:

SELECT store, COUNT(DISTINCT saledate), CountNov, CountDec, SumNov, SumDec, (SumNov/CountNov) AS NovAvgRvn, (SumDec/CountDec) AS DecAvgRvn FROM 
(
    SELECT store, saledate, 
                CASE WHEN 
                        SUM(CASE EXTRACT(MONTH FROM saledate) 
                                WHEN '11' THEN amt END) IS NULL THEN 0 
                            ELSE SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN amt END)
                        END AS SumNov, 
                CASE WHEN SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN amt END) IS NULL THEN 0 
                            ELSE SUM(CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN amt END)
                        END AS SumDec, 
                CASE WHEN COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN saledate END) IS NULL THEN 0 
                            ELSE COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '11' THEN saledate END)
                        END AS CountNov, 
                CASE WHEN COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN saledate END) IS NULL THEN 0 
                            ELSE COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN '12' THEN saledate END)
                        END AS CountDec 
    FROM trnsact 
    WHERE stype = 'p' 
    GROUP BY store, saledate, SumNov, SumDec, CountNov, CountDec
) AS T1 
    WHERE CountDec > 0 AND CountNov > 0 
GROUP BY store, saledate, CountNov, CountDec, SumNov, SumDec, NovAvgRvn, DecAvgRvn
ORDER BY store;

Answer 2

Why are you using a nested query for this?

SELECT store, COUNT(DISTINCT saledate), 
       SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 11 THEN amt ELSE 0 END) as SumNov, 
       SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 11 THEN amt ELSE 0 END) as SumDec, 
       COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN 11 THEN saledate END) as CountNov, 
       COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN 12 THEN saledate END) as CountDec
FROM trnsact 
WHERE stype = 'p' 
GROUP BY store; 

Notes:

• COUNT() never returns NULL so there is no need for the CASE at all.
• With an ELSE clause, the SUM() doesn't return NULL either (there would have to be no rows matching for the SUM() to return NULL, and with no matching rows in the group, the group wouldn't exist).
• EXTRACT() returns a number, so compare to a number.

Answer 3

The GROUP BY in the Derived Table is not doing aggregation on a month level, thus the COUNT(DISTINCT saledate)will be 1. You shouldn't use strings for numeric data (result of EXTRACT). You don't need the CASE(SUM) because COUNT never returns a NULL (you might use COALESCE instead):

I assume you want a query like this instead:

SELECT store, 
   -- if you only need the dates from Nov & Dec you can simply do
   -- CountNov + CountDec instead 
   COUNT(DISTINCT saledate),

   SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 11 THEN amt ELSE 0 END) AS SumNov, 
   SUM(CASE EXTRACT(MONTH FROM saledate) WHEN 12 THEN amt ELSE 0 END) AS SumDec, 
   COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN 11 THEN saledate END) AS CountNov, 
   COUNT(DISTINCT CASE EXTRACT(MONTH FROM saledate) WHEN 12 THEN saledate END) AS CountDec, 
   (SumNov/CountNov) AS NovAvgRvn, 
   (SumDec/CountDec) AS DecAvgRvn
FROM trnsact 
WHERE stype = 'p' 
  -- don't you need a condition to filter for a specific year/month?
  AND EXTRACT(MONTH FROM saledate) IN (11,12) 
GROUP BY store 
ORDER BY store;