CODEWITHSUNDEEP
teradata
Rahul Gupta
Rahul Gupta

Reputation: 1

Error while running Query on Teradata-Selected non-aggregate values must be part of the associated group

 My Query:
 SELECT Case When msa_high>=50 AND msa_high<=60 then 'low'
             When msa_high>60 AND msa_high>=70 then 'medium'
             When msa_high>70 then 'high' end as edu_lv , Sum(rev)/Sum(S_days) AS avg_sales                                        
 from store_msa left join(Select
 (EXTRACT(YEAR FROM t.saledate)||'-'||
 EXTRACT(MONTH FROM t.saledate) ||'-'||
 t.store ) AS mys,COUNT(DISTINCT t.saledate) AS S_days, sum(t.amt) AS rev
 From trnsact t
 Where (t.saledate)<'2005-08-01' AND t.stype='P' AND mys IN
(SELECT
(EXTRACT(YEAR FROM t.saledate)||'-'||
EXTRACT(MONTH FROM t.saledate) ||'-'||
t.store) AS mys
FROM trnsact t
HAVING COUNT(DISTINCT t.saledate)>20
GROUP BY mys,t.store)) AS rev
ON store_msa.store=rev.store
Group By edu_lv,avg_sales
Order By avg_sales;

Can some one point out what parameter has been missed to find a solution to this error and secondly which Group By statement is generating this error.

Upvotes: 0

Views: 228

Answers (1)

dnoeth
dnoeth

Reputation: 60472

The are two problems, see my inline comments (after formatting the surce code to make it readable)

SELECT 
   Case 
      When msa_high>=50 
         AND msa_high<=60 
      then 'low'
      When msa_high>60 
         AND msa_high>=70 
      then 'medium'
      When msa_high>70 
      then 'high' 
   end as edu_lv , 
   Sum(rev)/Sum(S_days) AS avg_sales
from 
   store_msa 
left join 
  ( Select
      (EXTRACT(YEAR FROM t.saledate)|| '-'||
         EXTRACT(MONTH FROM t.saledate) || '-'||
         t.store ) AS mys, 
      COUNT(DISTINCT t.saledate) AS S_days, 
      sum(t.amt) AS rev
   From 
      trnsact t
   Where (t.saledate)<'2005-08-01' 
      AND t.stype='P' 
      AND mys IN
       ( SELECT
            (EXTRACT(YEAR FROM t.saledate)|| '-'||
              EXTRACT(MONTH FROM t.saledate) || '-'||
              t.store) AS mys
         FROM 
            trnsact t
         HAVING 
            COUNT(DISTINCT t.saledate)>20
         GROUP BY 
            mys, 
            t.store 
       ) 
    -- ***** missing GROUP BY, probably mys, store again? *****
  ) AS rev ON store_msa.store=rev.store
Group By 
   edu_lv, 
   avg_sales  -- ***** remove this, can't group by an aggregated column *****
Order By 
   avg_sales;

Upvotes: 1

Related Questions