Group By in a Case Statement 3

[tshad]

Can you put a "Group By" clause in a Case statement?

I have a query that needs both. I can put 2 statements in a stored procedure and call the stored procedure if necessary (one with a group by and one without).

For example:

IF @Grouped = 1
BEGIN
   SELECT Name, SUM(Quantity)
   FROM TableA
   Group by Name
END
ELSE
BEGIN
   SELECT Name, Quantity
   FROM TableA
END

Would like to do something like:

SELECT NAME, CASE WHEN @Grouped = 1 THEN SUM(Quantity) ELSE Quantity END
FROM TableA
CASE WHEN @Grouped = 1 THEN Group BY Name END

I could do this using dynamic SQL but would rather not.

Thanks,

Tom

 

[gmmastros]

No. You cannot put a group by within a case statement. I suspect that what you are trying to accomplish is possible by another means. Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tshad]

Here is an example of what I am trying to accomplish (the actual Querys are more complicated).

If OBJECT_ID('tempdb..#Stage') IS NOT NULL
	DROP TABLE #Stage


If OBJECT_ID('tempdb..#Ship') IS NOT NULL
	DROP TABLE #Ship


CREATE TABLE #Stage
(
	StageID int,
	ShipNumber varchar(10),
	PONumber varchar(10),
	iQty int
)

CREATE TABLE #Ship
(
	ShipID int,
	ShipmentNumber varchar(10),
	Units int
)

INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (20, 'MZ12345', 'A4735', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (22, 'MZ12345', 'A4735', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (23, 'MZ12345', 'A4735', 10)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (35, 'MZ12345', 'J3321', 40)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (36, 'MZ12345', 'J3321', 25)
INSERT #Stage (StageID, ShipNumber, PONumber, iQty) VALUES (32, 'MZ12345', 'V3199', 10)

DECLARE @Group bit

SELECT @Group = 0

IF @Group = 0
BEGIN
	SELECT PONumber, iQty
	FROM #Stage st
END
ELSE
BEGIN
	SELECT PONumber, SUM(iQty) AS iQty
	FROM #Stage st
	GROUP BY PONumber
END

If @Group = 0 the results would be:

PONumber	iQty
A4735		10
A4735		25
A4735		10
J3321		40
J3321		25
V3199		10

If @Group = 1, the results would be:

PONumber	iQty
A4735		45
J3321		65
V3199		10

The query would be in a procedure and @Group would be passed in. I can do it this way with the "IF" statement but would like to do it in one statement.

Thanks,

Tom

 

[Olaf Doschke]

You can't do GROUP BY in a CASE, but you can do a CASE in the GROUP BY clause:

SELECT PONumber, SUM(iQty) AS iQty
	FROM #Stage
	GROUP BY PONumber, CASE WHEN @Group = 0 THEN StageID END;

This way you conditionally also group by a secondary column (StageID in this case) that is unique per record. Therefore this does make every record a single group and that works like not grouping data.

Bye, Olaf.

 

[tshad]

That works fine.

Thanks

 

[DaveInIowa]

Great tip Olaf for a couple of reasons; 1) I didn't know you could do a CASE in the GROUP BY clause, and 2) I never thought about grouping by columns not in the SELECT clause.

 

[Olaf Doschke]

Thanks, Dave.

Since T-SQL allows you to do almost everything almost anywhere it's easier to know what you can not do than what you can
It seldom makes sense to group by some column not in the result, indeed you will just see more groups then, not seeing the group head data, but of course since the sql engine has tables at hand you can group by all available columns in the same manner as you can join by columns not in the result. In joins it's quite common, since surrogate keys are widely used and not wanted in results anyway, but they exist for joining and can be used for grouping, too. So there you also have one good case. If you want to group the address count of persons you may group by person lastname, firstname, but rather should group by personid, even if not in the result.

Bye, Olaf.