Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-10-2018
11:27 AM

03-10-2018
11:27 AM

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

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

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
09:33 AM

03-11-2018
09:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
05:10 AM

03-11-2018
05:10 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
07:20 AM

03-11-2018
07:20 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
09:33 AM

03-11-2018
09:33 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
09:32 PM

03-11-2018
09:32 PM

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

;

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

;

Highlighted
##
##### Re: How group by executed with case

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-11-2018
11:58 PM

03-11-2018
11:58 PM

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)