Group By Query With Rollup - 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 2000 Forums

Transact-SQL (2000)

Group By Query With Rollup

Author

Topic

chadbryant5
Starting Member

32 Posts

Posted - 2005-04-19 : 11:25:07

Hi, I was reading the Sql Server Books Online help about the ROLLUP Operator.

It gives an example that is very close to what I'm trying to accomplish.

It lists the following:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00

I want exactly this except I ONLY want the rows where Color = ALL And Item <> ALL... in other words I just want the two subtotals for Chair and Table as the rows returned by the query so I'd know Chair had 311 total and Table had 347. I'd then want to sort by that subtotal count DESC so that Table listed first since it had more.

Is this possible?

Thanks in advance!!!

Chad

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-19 : 14:24:09

Would just doing this suffice?

SELECT Item, 'ALL' AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item
ORDER BY QtySum DESC

chadbryant5
Starting Member

32 Posts

Posted - 2005-04-19 : 15:22:17

Yes, something like that is better. I guess I was making harder than it needed to be! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources