How group by executed with case

Database
Highlighted

How group by executed with case

Can someone explain me
X Y
10 30
10 50
30 30
20 100
20 200

Sel x ,sum (y) from table
Group by case when x in (10,20,30) then 1
End ;

Error: aggregated column should be part of associated group

Just want to understand as the case statement output will be 1 so it is group by 1 then why 1st column is not considered as part of aggregated group ,pls explain

Accepted Solutions
Junior Contributor

Re: How group by executed with case

The result of the CASE is different from the data in column x.

When x is one of the values 10,20 or 30 the Case will return 1,2 or 3, but there might be actual data with those values,too.

And when it's neither 10,20 nor 30 the Case will return NULL, which is also not the same as the original value.

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: How group by executed with case

Well, it's simply not valid SQL.

The 1st column is either 10,20 or 30, which of those values should be returned?

Before you edited the question it looked like this was MySQL  (but an ELSE part was missing), which allowed quite dumb things in older releases :-)

 

When you repeat the CASE in the Select it will work.

 

 

 

 

Re: How group by executed with case

Thanks dnoeth ,
I agree with you that's the reason I edited my question actually I am working on teradata and later i realise that these scenarios is not working on teradata.

Its correct that for earlier we have 1 group so its difficult that which one to pick 10,20,30?

Yes this query will run if case will be there in select.But if the case will be like that,just want to understand this scenarios why that is not working

Sel x,sum(y)
From table
Group by case when x=10 then 1
When x=20 then 2
When x=30 then 3
End;
But still it's failing
My main concern I want to learn on group by instead of this query output.
Junior Contributor

Re: How group by executed with case

The result of the CASE is different from the data in column x.

When x is one of the values 10,20 or 30 the Case will return 1,2 or 3, but there might be actual data with those values,too.

And when it's neither 10,20 nor 30 the Case will return NULL, which is also not the same as the original value.

Re: How group by executed with case

Thanks dnoeth ,very nice explanation :-)
Agreed with you that we might have some value which will not make proper group.

Based on that I can see below two cases 1st one follow all the grouping thing so that will be fine.
But why second one is failing as now we cant have value similar to 1,2,3 for x ,neither we have null group coming for x as where will run before group by clause.

1st scenario :
Sel case when x=10 then 1
When x=20 then 2
When x=30 then 3 end ,sum(y)
From table
Group by case when x=10 then 1
When x=20 then 2
When x=30 then 3
End
;
2nd scenario:
Sel x,sum(y) from table
Where x in (10,20,30)
Group by case when x=10 then 1
When x=20 then 2
When x=30 then 3
End
;
Junior Contributor

Re: How group by executed with case

Because when #2 runs without error message today and you add 40 to your Where-condition tomorrow and then it fails you will complain about it :-)

 

The optmizer doesn't pre-calculate all possibe variations of the CASE output (and in most cases this will be impossible anyway)