sum into new row

Question

Post reply

sum into new row

cpinky01

Ten Centuries

Points: 1144
More actions
August 31, 2010 at 6:44 pm

#228514

After doing all the joins my final result set is something like this
This has records description, count column
Rerecords Description Count
Bank Records ------- 50
Hospital Records ----100
I need one more row where the total records which is the sum of bank records and hospital records ie here 150.
Rerecords Description ----Count
Bank Records ------50
Hospital Records -------100
Total Records ---------150
how to write query for this?
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Lutz already showed you how to do this over here. Especially relevant is the ROLLUP clause of the SELECT statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

cpinky01 Ten Centuries Points: 1144 More actions · Answer 2

I tried to do roll up but i could not get the result what i needed.

Let me know if theres is any way i can do it.

Thanks

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 3

Try this:

SELECT (CASE WHEN RecordDescription IS NULL THEN 'Total records' ELSE RecordDescription END) as Records, SUM(Count) from Table1 GROUP BY RecordDescription WITH ROLLUP

cpinky01 Ten Centuries Points: 1144 More actions · Answer 4

Hi Tarun Jaggi,

I have converted the result set into pivot table hence problem solved. Thank you very much for your reply.

THnaks