group by w/rollup within group by

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 w/rollup within group by

Author

Topic

skatermel
Starting Member

3 Posts

Posted - 2005-05-31 : 15:34:23

I am wondering if there is a way to get the query results I need in 1 SQL stmt. I have the following query:

SELECT CASE WHEN GROUPING(pur.[Description]) = 1 THEN 'Total'
ELSE pur.[Description] END AS 'Description'
, COUNT(InspectionID) AS 'Total'
, ServiceIDText
FROM tblInspection ins JOIN lkupInspectionPurpose pur
ON ins.Purpose = pur.[ID]
WHERE BeginDate BETWEEN '1/1/2005' AND '1/15/2005' -- params
GROUP BY pur.[Description], ins.ServiceIDText WITH ROLLUP

which produces the following results:
Description Count Service
Complaint Investigation 8 01
Complaint Investigation 1 03
Complaint Investigation 3 04
Complaint Investigation 6 14
Complaint Investigation 2 16
Complaint Investigation 20 NULL
Consultation 3 14
Consultation 3 NULL
In-Office Review 10 01
In-Office Review 6 02
In-Office Review 1 03
In-Office Review 3 05
In-Office Review 1 07
In-Office Review 1 08
In-Office Review 4 14
In-Office Review 1 16
In-Office Review 27 NULL
Scheduled Review 1 NULL
Scheduled Review 1 02
Scheduled Review 2 14
Scheduled Review 4 NULL
Unannounced Visit 1 NULL
Unannounced Visit 11 01
Unannounced Visit 7 02
Unannounced Visit 1 03
Unannounced Visit 3 04
Unannounced Visit 2 05
Unannounced Visit 3 07
Unannounced Visit 1 08
Unannounced Visit 4 14
Unannounced Visit 33 NULL
Total 87 NULL

What I need is the SQL to produce the following:
Service 01 then the listing of the description categories with totals.
Service 02 then the listing ...

Is this possible?

Thanks,
Melissa

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-31 : 17:46:05

Sample data would help me understand this.

Could you expand the wanted result recordset? I don't understand what "then the listing of the description categories with totals" is exactly. I could make some inferences, but a sample output, with 3 or 4 rows would help.

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-05-31 : 18:21:30

Do some searching on CrossTab or Dynamic CrossTab.
I belive that's what you are looking for here.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

skatermel
Starting Member

3 Posts

Posted - 2005-06-01 : 09:00:17

Thanks. Got it from BOL.

skatermel
Starting Member

3 Posts

Posted - 2005-06-01 : 09:07:57

Actually, what I had originally was correct. I just needed to switch my cols in the GROUP BY clause. No case stmt needed and I can using the GROUPING for a label on my totals.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-01 : 09:16:54

Remember -- don't try to do presentation things in SQL Server. SQL is a database server, it's only job is to return rows of raw data. It is the presentations layer's job to format, subtotal and group the results into a presentable format.

Keep things simple and don't try to use a screw driver to hammer nails. use the right tool for the job it was intended.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources