how group by works - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how group by works

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-01 : 00:59:49

[code]DECLARE @tempfun TABLE
(
col1 varchar(5),
col2 varchar(5)
)
INSERT @tempfun VALUES('aaa','bbb')
INSERT @tempfun VALUES('aaa','ccc')
INSERT @tempfun VALUES('aaa','ddd')
INSERT @tempfun VALUES('bbb','bbb')
INSERT @tempfun VALUES('bbb','ccc')
INSERT @tempfun VALUES('bbb','ddd')

SELECT *
FROM (
SELECT col1, CASE WHEN col2='bbb' then 'ccc' else col2 END col2
FROM @tempfun)a
GROUP BY col1, col2[/code]This return
aaa,ccc
aaa,ddd
bbb,ccc
bbb,ddd

[code]SELECT col1, CASE WHEN col2='bbb' then 'ccc' else col2 END col2
FROM @tempfun
GROUP BY col1, col2[/code]
This return
aaa,ccc
aaa,ccc
aaa,ddd
bbb,ccc
bbb,ccc
bbb,ddd

Weird when group by must comply with select column but they seem to be group by from table instead of selected column

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 01:03:54

basically, the column in the SELECT list that is not aggregated (SUM, MAX, MIN etc) must appear in the GROUP BY


SELECT cola, colb, colc, sum(cold), sum(cole)
from   sometable
group by cola, colb, colc

KH
[spoiler]Time is always against us[/spoiler]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-01 : 01:07:30

this is exactly the expected behavior. in your 1st example, you're changing the values in col2 and then grouping by the derived table.

in your 2nd example, you're grouping by the original source table

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-01 : 01:07:45

[code]DECLARE @tempfun TABLE
(
col1 varchar(5),
col2 varchar(5),
Col3 INT
)
INSERT @tempfun VALUES('aaa','bbb',1)
INSERT @tempfun VALUES('aaa','ccc',2)
INSERT @tempfun VALUES('aaa','ddd',3)
INSERT @tempfun VALUES('bbb','bbb',4)
INSERT @tempfun VALUES('bbb','ccc',5)
INSERT @tempfun VALUES('bbb','ddd',6)

SELECT col1, col2, SUM(col3) col3
FROM (
SELECT col1, CASE WHEN col2='bbb' then 'ccc' else col2 END col2, col3
FROM @tempfun)a
GROUP BY col1, col2[/code]
This is what i meet sometime...

Hope can help...but advise to wait pros with confirmation...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-01 : 01:08:41

shouldn't be grouping when there's no aggregates

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-01 : 01:13:39

just hope that group by is grouping according the select statement...instead of group with the data from the original table...

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 01:52:29

[code]
SELECT col1, CASE WHEN col2='bbb' then 'ccc' else col2 END col2, sum(col3) col3
FROM @tempfun
group by col1, CASE WHEN col2='bbb' then 'ccc' else col2 END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-01 : 04:26:08

just a discussion more to a question....don't you think is better to group by selected column more to gorup by data in table...

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-01 : 06:16:02

quote:
Originally posted by waterduck

just a discussion more to a question....don't you think is better to group by selected column more to gorup by data in table...

Hope can help...but advise to wait pros with confirmation...

Sorry . . don't quite understand your question here

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-02 : 12:29:15

hehehe sorry late reply...i mean i just wanna talk talk....not asking any question

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources