when to use (case grouping when ........)

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

General SQL Server Forums

New to SQL Server Programming

when to use (case grouping when ........)

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-06-22 : 09:25:16

im very new to sql programming. I wonder at which situation we use codes something like below

case grouping(field) when..........

I come across many such examples from Google when i try to learn subtotal, crosstab. Also I have one in one of my threads.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162981

I would like to know wen we use "grouping" particularly.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-22 : 11:36:03

From Books Online:

quote:
GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.

Basicallt it allows you to know which row is a "Grouping" row, so you can deal with appropriatly if you want. for example you could replace the NULL with a string like Total or something. For example:

DECLARE @T TABLE (ColA INT, ColB INT)
INSERT @T VALUES
(1,	1),
(1,	2),
(1,	3),
(2,	2),
(3,	4),
(3,	5),
(5,	5),
(5,	8),
(6,	6),
(7,	3),
(7,	8)

SELECT ColA, SUM(ColB)
FROM @T
GROUP BY ColA
WITH ROLLUP

SELECT CASE WHEN GROUPING(ColA) = 1 THEN 'Total' ELSE CAST(ColA AS VARCHAR(20)) END AS ColA, SUM(ColB), GROUPING(ColA)
FROM @T
GROUP BY ColA
WITH ROLLUP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:33:13

mostly used in cases where you return data grouped on multiple column combinations and would like to apply some conditional logic and determine display options/retrieve value of another field based on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-06-23 : 06:57:55

Thanks for reply
I'm still confused a bit. Maybe I can understand once I see the output and difference myself.

For that, i tried to copy and paste entire @Lamprey code in my sql server management studio 2005 under "new query" window. When i execute, it get error "incorrect syntax...". Am I pasting it in wrong area?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-23 : 13:34:11

quote:
Originally posted by learning_grsql

Thanks for reply
I'm still confused a bit. Maybe I can understand once I see the output and difference myself.

For that, i tried to copy and paste entire @Lamprey code in my sql server management studio 2005 under "new query" window. When i execute, it get error "incorrect syntax...". Am I pasting it in wrong area?

are you using sql 2008 or above? Lampreys insert code works only on sql 2008 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-06-23 : 15:32:05

Mine is sql 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-23 : 18:37:13

then modify it like


DECLARE @T TABLE (ColA INT, ColB INT)
INSERT @T 
SELECT 1,	1 UNION ALL
SELECT 1,	2 UNION ALL
SELECT 1,	3 UNION ALL
SELECT 2,	2 UNION ALL
SELECT 3,	4 UNION ALL
SELECT 3,	5 UNION ALL
SELECT 5,	5 UNION ALL
SELECT 5,	8 UNION ALL
SELECT 6,	6 UNION ALL
SELECT 7,	3 UNION ALL
SELECT 7,	8

SELECT ColA, SUM(ColB)
FROM @T
GROUP BY ColA
WITH ROLLUP

SELECT CASE WHEN GROUPING(ColA) = 1 THEN 'Total' ELSE CAST(ColA AS VARCHAR(20)) END AS ColA, SUM(ColB), GROUPING(ColA)
FROM @T
GROUP BY ColA
WITH ROLLUP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-06-24 : 10:55:26

Great! Thank you. It's working now.
But I have a question on this. What exactly the statement below does in this code?

else cast((col A as Varchar (20))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 12:59:58

its making datatype of ColA as varchar. Reason is CASE WHEN can return only singe datatype value. So since you want word Total to appear for total row you've to convert the other values of ColA also to varchar (its INT in table)which is why you've apply CAST

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-06-25 : 11:22:14

Thank you...I have understood it completely now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources