Teradata - What is wrong with this query? "Selected non-aggregate values must be part of the associated group."

Question

I am currently working on an sql problem using teradata, but I keep getting the error message although I can't find any problems. Here is my query :

SELECT TOP 10 d.deptdesc, tt.store, SUM(tt.amt11) AS sum11, SUM(tt.amt12) AS sum12,
        SUM(tt.num11) AS num11, SUM(tt.num12) AS num12,
        sum11/num11 AS avg11, sum12/num12 AS avg12,
        (avg12-avg11)/avg11 * 100 AS increase
FROM(SELECT store, 
        SUM(CASE monthId WHEN 11 THEN total_revenue END) AS amt11,
        SUM(CASE monthId WHEN 12 THEN total_revenue END) AS amt12,
        SUM(CASE monthId WHEN 11 THEN date_num END) AS num11,
        SUM(CASE monthId WHEN 12 THEN date_num END) AS num12
    FROM(SELECT EXTRACT(MONTH FROM saledate) AS monthId,
                EXTRACT(YEAR FROM saledate) AS yearId,
                store,
                SUM(amt) AS total_revenue,
                COUNT(DISTINCT saledate) AS date_num       
            FROM trnsact
            WHERE stype = 'P' AND NOT(monthId = '8' AND yearId = '2005') 
            GROUP BY monthId, yearId, store
            HAVING date_num >= 20) t
    GROUP BY store) tt 
INNER JOIN (SELECT sku, store FROM trnsact) ttt ON tt.store = ttt.store 
INNER JOIN skuinfo sku ON ttt.sku = sku.sku
INNER JOIN deptinfo d ON sku.dept = d.dept
GROUP BY d.deptdesc, tt.store
HAVING sum11 > 1000 AND sum12 > 1000
ORDER BY increase DESC;

Teradata gives out the message like this

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.

Could you give me any advice? Thanks in advance!

Answer 1

The parser check for non aggregated column you alias for aggregated but the You could not use alias for select column directly in select so you should use :

SELECT TOP 10 
  d.deptdesc
  , tt.store
  , SUM(tt.amt11) AS sum11
  , SUM(tt.amt12) AS sum12
  , SUM(tt.num11) AS num11
  , SUM(tt.num12) AS num12
  , SUM(tt.amt11)/SUM(tt.num11) AS avg11
  , SUM(tt.amt12)/SUM(tt.num12) AS avg12
  , (SUM(tt.amt12)/SUM(tt.num12)-SUM(tt.amt11)/SUM(tt.num11))/SUM(tt.amt11)/SUM(tt.num11) * 100 AS increase
  ....

Answer 2

Use this query:

SELECT TOP 10  deptdesc, store, sum11, sum12, num11, num12,
            sum11/num11 AS avg11, sum12/num12 AS avg12,
            ((sum12/num12)-(sum11/num11))/(sum11/num11)* 100 AS increase FROM
(
    SELECT d.deptdesc, tt.store, SUM(tt.amt11) AS sum11, SUM(tt.amt12) AS sum12,
            SUM(tt.num11) AS num11, SUM(tt.num12) AS num12
    FROM(SELECT store, 
            SUM(CASE monthId WHEN 11 THEN total_revenue END) AS amt11,
            SUM(CASE monthId WHEN 12 THEN total_revenue END) AS amt12,
            SUM(CASE monthId WHEN 11 THEN date_num END) AS num11,
            SUM(CASE monthId WHEN 12 THEN date_num END) AS num12
        FROM(SELECT EXTRACT(MONTH FROM saledate) AS monthId,
                    EXTRACT(YEAR FROM saledate) AS yearId,
                    store,
                    SUM(amt) AS total_revenue,
                    COUNT(DISTINCT saledate) AS date_num       
                FROM trnsact
                WHERE stype = 'P' AND NOT(monthId = '8' AND yearId = '2005') 
                GROUP BY monthId, yearId, store
                HAVING date_num >= 20) t
        GROUP BY store) tt 
    INNER JOIN (SELECT sku, store FROM trnsact) ttt ON tt.store = ttt.store 
    INNER JOIN skuinfo sku ON ttt.sku = sku.sku
    INNER JOIN deptinfo d ON sku.dept = d.dept
    GROUP BY d.deptdesc, tt.store
) AS T
    GROUP BY deptdesc, tt.store, sum11, sum12, num11, num12
    HAVING sum11 > 1000 AND sum12 > 1000
    ORDER BY increase DESC;