# 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
------------------------------------------------------------------------------------------------

``````
###### Who is Participating?

Commented:
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

Senior .Net DeveloperCommented:
you could do a union all and then do the query without group by's.
0

Microsoft 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

Commented:
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

MSSQL Senior EngineerCommented:
0

Commented:
Look into 'rollup' and 'cube' t-sql statements.
0

Commented:
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          )
``````
0

Author 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);
``````
0

Commented:
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

Author 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
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.