Additional columns in a GROUP BY query

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)

Additional columns in a GROUP BY query

Author

Topic

Freshies4eva
Starting Member

7 Posts

Posted - 2005-09-20 : 11:38:37

Hi,
I'm trying to display data like this:

ID Name Count
10 Dallas 437
21 Chicago 568
45 New York 438
-1 (Total) 1443

But I get this...
ID Name Count
10 Dallas 437
-1 Dallas 437
21 Chicago 568
-1 Chicago 568
45 New York 438
-1 New York 438
-1 (Total) 1443

How can I rework this query to accomplish the desired result? I need to display the ID for each name. Thanks for your help!

SELECT
CASE GROUPING(l.num)
WHEN 0
THEN l.num
ELSE
-1
END AS 'ID',
Case GROUPING(l.name)
WHEN 0
THEN l.name
ELSE
'(Total)'
END AS 'Name',

COUNT(*) AS 'Units'
FROM inventory i INNER JOIN locations l ON i.location = l.num
WHERE i.inv_date = '9/18/2005'
GROUP BY l.name, l.num WITH ROLLUP

Freshies4eva
Starting Member

7 Posts

Posted - 2005-09-21 : 12:36:46

I found the answer I was searching. I didn't understand HAVING. Since it filters the results after the GROUP BY WITH ROLLUP operation you can filter out the summarized rows you don't want. Once I added the line below I removed the summaries on number and but kept the summary on name . This eliminated the duplicate rows that generated as a result of including number in the group by clause.

HAVING (GROUPING(l.name) = 1) or (GROUPING(l.num) = 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources