CODEWITHSUNDEEP
sqlsql-servert-sql
JM1
JM1

Reputation: 1715

TSQL: How to add a totals row

I'm on SQL Server 2014 and I have a list of building ID's with various totals columns. I would like to add a row of totals for the building.

Can the following desired output be done in SQL? If so, what's the best way? I need to learn how to this for various projects. I don't need LevelID totals, just for the other columns, and I'm not sure if we can add the text 'BuildingTotal' in the totals row.

Desired Output:

enter image description here

SQL Fiddle:

Here is the code for a test table in case SQL Fiddle doesn't work. 

CREATE TABLE EMPLOYEES
(
BuildingID Numeric,
LevelID Numeric,
FemaleEmp Numeric,
MaleEmp Numeric,
TotalEmpByLevel Numeric
)

INSERT INTO EMPLOYEES
(BuildingID, LevelID, FemaleEmp, MaleEmp, TotalEmpByLevel)
VALUES 
(111, 1,91, 89, 180),
(111, 2,98, 94, 192),
(111, 3,94, 113 , 207),
(111, 4,110, 119, 229),
(111, 5,107, 9, 203),
(113, 1,53, 4, 101),
(113, 2,51, 5, 106),
(113, 3,68, 5, 119),
(113, 4,58, 6, 118),
(113, 5,57, 6, 117),
(114, 1,25, 3, 56 ),
(114, 2,26, 3, 63 ),
(114, 3,32, 2, 61 ),
(114, 4,27, 3, 58 ),
(114, 5,26, 2, 49 ),
(116, 1,84, 102 , 186),
(116, 2,83, 92, 175),
(116, 3,89, 87, 176),
(116, 4,71, 91, 162),
(116, 5,87, 72, 159)

Thank you for your help!

Upvotes: 0

Views: 119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method is to use grouping sets or with rollup. This requires a little trick, though. You need an aggregation query:

select BuildingId, LevelId, sum(FemaleEmp) as FemaleEmp,
       sum(MaleEmp) as MaleEmp, sum(TotalEmpByLevel) as TotalEmpByLevel
from employees
group by grouping sets((BuildingId, LevelId), (BuildingId));

You can add the word Total to the lines. This is a bit tricky, because the types are different:

select (case when grouping(LevelId) = 1 then 'Building Total' else cast(BuildingId as varchar(255)) end) as BuildingId,
       LevelId, sum(FemaleEmp) as FemaleEmp,
       sum(MaleEmp) as MaleEmp, sum(TotalEmpByLevel) as TotalEmpByLevel
from employees
group by grouping sets((BuildingId, LevelId), (BuildingId));

Upvotes: 2

starko
starko

Reputation: 1149

Try Group by with rollup

This is link with example

Upvotes: 2

Related Questions