how to display totals grand total in t-sql

Question

how to display totals grand total in t-sql

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 1

Alan.B (11/20/2013)
clayman (11/20/2013)
Jeff Moden (11/19/2013)
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,
i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement.
I agree that this is something that can be done quite easily using SSRS. I work on SSRS every day and allowing the application perform the sorting and grouping is one way to dramatically speed up T-SQL queries. If I were using the data in the OP to create an SSRS report I would certainly allow the application to do the sorting, especially if there is any filtering done inside the report.
That said, this is a T-SQL forum which is why the OP question was not asked in the SSRS forum.
And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:
It's not rocket science in t-sql either. Here's my detailed instructions for accomplishing this using t-sql:
1. Read Microsoft SQL Server 2012 T-SQL Fundimentals by Itzek Ben-Gan (page 234)*
2. Do this**:
SELECTISNULL(classname, 'GRAND TOTAL'),
section,
SUM(marks)
FROM dbo.class
GROUP BY
GROUPING SETS
(
(classname, section, marks),
(classname, section),
()
);
3. Look at the linear query execution plan and smile (optional) :smooooth:
* The solution works for 2008+
** The "TOTAL" requirement excluded for brevity

Crud. You beat me to it (except for the TOTAL requirement). I've been head down into the hurricane known as work and haven't had access to my 2K8 machine to demo GROUPING SETS. Well done, Alan. Excellent reference to Ben-Gan's fine book, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 2

pietlinden (11/19/2013)
If you're fairly new to SQL, I would do this in SSRS, because you can get SSRS to do pretty much all the hard work for you (sorting, grouping, subtotals, totals...)
I did it by
1. creating a new report
2. adding a table to my report surface
3. creating a data source pointing to TEMPDB (you would point to the database that contains your data... I'm just using a UNION query based on the data provided.)
4. creating a dataset with the data provided.
Mine looks something like this:
SELECT 'first' AS Grp1, 'a' AS Grp2, 800 AS 'Value'
UNION ALL
SELECT 'first', 'a', 200
UNION ALL
SELECT 'first', 'b', 100
UNION ALL
SELECT 'first', 'b',200
UNION ALL
SELECT 'second', 'a', 400
UNION ALL
SELECT 'second', 'b', 400
(Aside: Is it possible to upload RDL files? Looks like not...)
Basically, I added Marks to the tablix, and deleted the other columns.
Then add a Row Group by Section.
Then add a second Row Group by ClassName.
add footers for both.
In the Footers, add SUM(Marks).
I added my groups above left, but you could do adjacent left...

There it is! Thank you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Jeff Moden (11/20/2013)
Alan.B (11/20/2013)
clayman (11/20/2013)
Jeff Moden (11/19/2013)
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,
i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement.
I agree that this is something that can be done quite easily using SSRS. I work on SSRS every day and allowing the application perform the sorting and grouping is one way to dramatically speed up T-SQL queries. If I were using the data in the OP to create an SSRS report I would certainly allow the application to do the sorting, especially if there is any filtering done inside the report.
That said, this is a T-SQL forum which is why the OP question was not asked in the SSRS forum.
And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:
It's not rocket science in t-sql either. Here's my detailed instructions for accomplishing this using t-sql:
1. Read Microsoft SQL Server 2012 T-SQL Fundimentals by Itzek Ben-Gan (page 234)*
2. Do this**:
SELECTISNULL(classname, 'GRAND TOTAL'),
section,
SUM(marks)
FROM dbo.class
GROUP BY
GROUPING SETS
(
(classname, section, marks),
(classname, section),
()
);
3. Look at the linear query execution plan and smile (optional) :smooooth:
* The solution works for 2008+
** The "TOTAL" requirement excluded for brevity
Crud. You beat me to it (except for the TOTAL requirement). I've been head down into the hurricane known as work and haven't had access to my 2K8 machine to demo GROUPING SETS. Well done, Alan. Excellent reference to Ben-Gan's fine book, as well.

Thank you Jeff! 🙂 I never heard of GROUPING SETS until reading about it in Ben-Gan's book. I just started trying to learn it (it took me some getting used to) but it's been very useful.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 4

kiril.lazarov.77 (11/20/2013)
Jeff Moden (11/19/2013)
clayman (11/19/2013)
dastagiri16 (11/19/2013)
hi,
i need the text like "TOTAL" ...rollup wont give it...
Please advice
Use SSRS
BWAAAA-HAAAA!!!! Sure it's easy to get to the moon. All you have to do is build a rocketship! 😉
Since the OP is a self-admitted newbie at SQL Server, please post detailed instructions on how to accomplish this task using SSRS. :w00t:
Personally I would have done this in SSRS because I know creating a row group or two is fairly easy. Also, a lot more flexible than the t-sql solution for this particular requirement. And no, groups in SSRS are not rocket science at all. Detailed instructions to create a row group? Are you kidding me? :w00t:

Now, why would I kid you? What if someone needs to get the job done and they've never used SSRS before? Are you beyond explaining the full up solution to newbies that haven't ever used SSRS before? And let's see how easy it is to move the SSRS solution from Dev to Staging to Prod. No changes are required in the T-SQL solution. Is the same true for SSRS? Not likely. At the very least, you'd have to change a connection instead of doing just a copy of the code of a promotion of a single stored procedure. 😉

Heh... and what makes you think that SSRS is more flexible here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)