CODEWITHSUNDEEP
mysqlsqlteradata
Bach Nguyen
Bach Nguyen

Reputation: 1

Teradata SQL with non-aggregated fields in GROUP BY

I have the code below that the Teradata will not work, and return the message as 'Selected non-aggregate values must be part of the associated group.' I think I have made sure all non-aggregated fields have been put in GROUP BY Clauses already. Can you help me take a look why its not working?

SELECT  str.store,str.city, str.state, dep.deptdesc, 100*(Clean.DecSales/Clean.DecSaleDays-Clean.NovSales/Clean.NovSaleDays)/(Clean.NovSales/Clean.NovSaleDays) AS PercentageChange

FROM ((skuinfo sku LEFT JOIN deptinfo dep ON sku.dept=dep.dept)

LEFT JOIN 
(SELECT t.sku, t.store, COUNT(DISTINCT t.saledate) AS NumOfDays, SUM(t.amt) AS DailyTotal,

(CAST(EXTRACT(YEAR FROM t.saledate) AS VARCHAR(8)))||'.'||
      (CAST(EXTRACT(MONTH FROM t.saledate) AS VARCHAR(8)))||'.'||
      (CAST(t.store AS VARCHAR(8)))AS ComboID,

SUM(CASE WHEN EXTRACT(MONTH from t.saledate) =11 THEN t.Amt END) AS NovSales,

SUM(CASE WHEN EXTRACT(MONTH from t.saledate) =12 THEN t.Amt END) AS DecSales,

COUNT(CASE WHEN EXTRACT(MONTH from t.saledate) =11 THEN t.saledate END) AS NovSaleDays,

COUNT(CASE WHEN EXTRACT(MONTH from t.saledate) =12 THEN t.saledate END) AS DecSaleDays

FROM trnsact t

WHERE (t.saledate NOT BETWEEN '2005-08-01' AND '2005-08-31') AND t.stype='P'

GROUP BY t.sku, t.store

HAVING NumOfDays>19) Clean 

ON sku.sku=Clean.sku)

LEFT JOIN strinfo str ON str.store=Clean.store

GROUP BY str.store,str.city, str.state, dep.deptdesc;

Upvotes: 0

Views: 1516

Answers (2)

Rob Paller
Rob Paller

Reputation: 7786

Remove the GROUP BY on the outer query. Replace GROUP BY in the derived table Clean with an ordinal GROUP BY 1, 2, 5.

Something makes me believe the derived table's column ComboID, composed of SaleDate and StoreID because SaleDate is not part of the GROUP BY but is participating elsewhere in an aggregate of the derived table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is your SELECT:

SELECT str.store, str.city, str.state, dep.deptdesc,
       (100*(Clean.DecSales/Clean.DecSaleDays-Clean.NovSales/Clean.NovSaleDays)/(Clean.NovSales/Clean.NovSaleDays) ) AS PercentageChange

This is your GROUP BY:

GROUP BY str.store, str.city, str.state, dep.deptdesc

I see lots of column references in the SELECT that are not in the GROUP BY. I would guess that you intend either SELECT like this:

SELECT str.store, str.city, str.state, dep.deptdesc,
       SUM(100*(Clean.DecSales/Clean.DecSaleDays-Clean.NovSales/Clean.NovSaleDays)/(Clean.NovSales/Clean.NovSaleDays)
          ) AS PercentageChange

Or no GROUP BY at all in the outer query.

Upvotes: 1

Related Questions