Solved

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

Posted on 2014-09-02
328 Views
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
------------------------------------------------------------------------------------------------

``````
0
Question by:Varshini S

LVL 39

Expert Comment

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

LVL 65

Expert Comment

Please do a SELECT * FROM your table in SSMS, and copy-paste an image of the return set into this question.
0

LVL 73

Expert Comment

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

LVL 44

Expert Comment

0

LVL 1

Expert Comment

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

LVL 73

Expert Comment

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 Comment

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

LVL 73

Expert Comment

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 Comment

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

LVL 73

Accepted Solution

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

### Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…