Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

How to get the Sum of all column values in the last row of a resultset?

Is it possible to get the Sum of all column values in the last row of a result set for the  following table?

---------------------------------------------------------------------------------------------
Name      Avg        DOB                   Salary       Location      Bonus
-----------------------------------------------------------------------------------------------
AAA          31        02/19/1980        25,000          A                 45000
AA1          44        08/08/1982        35,000          A                 35000
--------------------------------------------------------------------------------------------                             
Total        75                                    60,000                              80000
---------------------------------------------------------------------------------------------
AA3          10          10/10/1975     25,000           B                 50000
AA4           30         12/10/1999     20,000           B                 25000
---------------------------------------------------------------------------------------------
Total         40                                  45,000                             75000
--------------------------------------------------------------------------------------------
Grand
Total         115                                1,05,000                        1,55,000
------------------------------------------------------------------------------------------------ 
                              

Open in new window

0
Varshini S
Asked:
Varshini S
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you could do a union all and then do the query without group by's.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please do a SELECT * FROM your table in SSMS, and copy-paste an image of the return set into this question.
0
 
sdstuberCommented:
I think something like this is what Kyle was alluding to...


   SELECT CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
             name,
         AVG,
         dob,
         salary,
         location,
         bonus
    FROM (SELECT * FROM yourtable
          UNION ALL
            SELECT NULL,
                   SUM(AVG) AVG,
                   NULL,
                   SUM(salary) salary,
                   location,
                   SUM(bonus) bonus
              FROM yourtable
          GROUP BY ROLLUP(location)) as x
ORDER BY location, name
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That it's your table?
As Jim Horn posted, we need to see your table to help you.
0
 
Irek PiwowarekCommented:
Look into 'rollup' and 'cube' t-sql statements.
0
 
sdstuberCommented:
rollup is how the query above works

on second look, the ordering could probably be improved

ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
         location,
         CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
         x.name;


I made the assumption "yourtable" looked something like this...
You would, of course,  user your actual table, but this CTE worked to give me a sample data set to work with.


WITH yourtable
     AS (SELECT 'AAA' name,
                31 AVG,
                '02/19/1980' dob,
                25000 salary,
                'A' location,
                45000 bonus          
         UNION ALL
         SELECT 'AA1',
                44,
                '08/08/1982',
                35000,
                'A',
                35000           
         UNION ALL
         SELECT 'AA3',
                10,
                '10/10/1975',
                25000,
                'B',
                50000         
         UNION ALL
         SELECT 'AA4',
                30,
                '12/10/1999',
                20000,
                'B',
                25000          )

Open in new window

0
 
Varshini SAuthor Commented:
This is my table

 
create table employee (Name varchar(50), avg1 numeric(5),dob datetime,
salary numeric(10,2),location varchar(1),bonus numeric(10,2));

insert into employee values ('AAA',31,'02/19/1980',25000,'A',45000);
insert into employee values ('AA1',44,'08/08/1982',35000,'A',35000);
insert into employee values ('AA3',10,'10/10/1975',25000,'B',50000);
insert into employee values ('AA4',30,'12/10/1999',20000,'B',25000);

Open in new window

0
 
sdstuberCommented:
ok, then the query I posted originally, with the updated order by should give you what you want


SELECT CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
             name,
         avg1,
         dob,
         salary,
         location,
         bonus
    FROM (SELECT * FROM yourtable
          UNION ALL
            SELECT NULL,
                   SUM(avg1) avg1,
                   NULL,
                   SUM(salary) salary,
                   location,
                   SUM(bonus) bonus
              FROM employee
          GROUP BY ROLLUP(location)) as x
ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
         location,
         CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
         x.name;
0
 
Varshini SAuthor Commented:
Hi sdstuber,
Query works well.  Is it possible to add sequence number for each group like below?

	name	avg1	dob	salary	location	bonus
1	AA1	44	00:00.0	35000	A	35000
2	AAA	31	00:00.0	25000	A	45000
	Total	75	NULL	60000	A	80000
1	AA3	10	00:00.0	25000	B	50000
2	AA4	30	00:00.0	20000	B	25000
	Total	40	NULL	45000	B	75000
	Grand Total	115	NULL	105000	NULL	155000

Open in new window

0
 
sdstuberCommented:
SELECT seq,
  CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
             name,
         AVG,
         dob,
         salary,
         location,
         bonus
    FROM (SELECT row_number() over(partition by location order by name) seq, t.* FROM yourtable t
          UNION ALL
            SELECT NULL,
                         NULL,
                   SUM(AVG) AVG,
                   NULL,
                   SUM(salary) salary,
                   location,
                   SUM(bonus) bonus
              FROM yourtable
          GROUP BY ROLLUP(location)) x
ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
         location,
         CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
         x.name;
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now