SQL Add total sum row for each property

Question

I'm trying to create a new total sum row for every state.

Sample table:

| State | Item | Amount
    A      X      100
    B      Y      200
    A      Z      100
    B      X      150

Result:

| State | Item | Amount
    A      X      100
    A      Z      100
 Total A          200
    B      Y      200
    B      X      150
 Total B          350

Is there SQL query that I can use to execute that table

Do you _specifically_ want to do it in SQL? Could you do it in your C# code instead (for example by `ORDER BY State` in SQL and the processing it in order in C#)? — mjwills, Apr 16 '20 at 08:07
What is your DBMS version? Some support a `WITH ROLLUP` clause on `GROUP BY` which could do what you want — Nick, Apr 16 '20 at 08:08
@mjwills Yes I can do it in C# code and right now I only loop the table just like in table 1. I've looked into this post too (https://stackoverflow.com/questions/17813580/add-a-new-row-to-datatable-for-sub-total) but I I dont know how to get the parameters from my SQL — Aidel Ibrahim, Apr 16 '20 at 08:14

Ajith · Answer 1 · 2020-04-16T08:26:13.187

2

Try below query.

SELECT * FROM states_YourtableName
    UNION
    SELECT 'Total '+[state] State ,'' Item ,SUM(Amount) Amount
    FROM states_YourtableName GROUP BY [State]

You will get an output as below

state   Item    Amount
A        X      100
A        Z      100
B        X      150
B        Y      200
Total A         200
Total B         350

edited Apr 16 '20 at 08:26

answered Apr 16 '20 at 08:20

Ajith

1,447
2
17
31

1

This should be `UNION ALL`. And then you should think of a way to order your result rows. With the current query you could get any order (e.g. Total B, A | Z, B | X | Total B, ...). Ordering by the first result column wouldn't work well either, because you'd get A, B, Total A, Total B, Z, Total Z for instance. – Thorsten Kettner Apr 16 '20 at 09:12

Nick · Accepted Answer · 2020-04-16T09:31:15.527

1

In SQL Server you can use ROLLUP on a GROUP BY clause to get intermediate and overall sums over the grouped by fields. In your case you would group by both state and item to get all rows:

SELECT CASE WHEN State IS NULL THEN 'Grand Total'
            WHEN Item IS NULL THEN CONCAT('Total ', State)
            ELSE State
       END AS State,
       Item, 
       SUM(Amount) AS Amount
FROM data
GROUP BY ROLLUP(State, Item)

Output:

State           Item    Amount
A               X       100
A               Z       100
Total A         (null)  200
B               X       150
B               Y       200
Total B         (null)  350
Grand Total     (null)  550

Demo on SQLFiddle

edited Apr 16 '20 at 09:31

answered Apr 16 '20 at 08:30

Nick

138,499
22
57
95

Does `ROLLUP` guarantee the result to be ordered thus? I tried to look this up, but haven't found the answer to this. In there docs (https://learn.microsoft.com/de-de/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15) they seem to take this order for granted without explicitly mentioning it. As far as I know, however, every query result is only guanranteed to be ordered when we apply an `ORDER BY` clause. – Thorsten Kettner Apr 16 '20 at 09:24
@ThorstenKettner agreed; the ordering is only implied by the documentation, but it really doesn't make any sense for it to be output any other way. – Nick Apr 16 '20 at 09:32

Thorsten Kettner · Answer 3 · 2020-04-16T09:17:12.927

The simplest way would be UNION ALL. If it suffices to show the state instead of 'Total <state>', this becomes:

select state, item, amount from mytable
union all
select state, null, sum(amount) from mytable group by state
order by state, case when item is null then 2 else 1 end, item;

Result:

State | Item | Amount
------+------+-------
    A |    X |    100
    A |    Z |    100
    A |      |    200
    B |    Y |    200
    B |    X |    150
    B |      |    350

score 0 · Answer 4 · answered Apr 16 '20 at 08:15

SQL is not working as an excel file, it is mostly used to store and process data. If you want to store that data(the Total) in SQL in my opinion the best way to do it is to create another table where you can keep your totals.

If you try just to print the totals in your screen you can check the link https://www.w3schools.com/sql/sql_count_avg_sum.asp

score 0 · Answer 5 · answered Apr 16 '20 at 08:35

You can use group by with Rollup

Try this -

declare @Data table
([State] varchar(20), Item varchar(20), Amount int )

Insert into @Data
values
(    'A',      'X',      100),
(    'B',      'Y',      200),
(    'A',      'Z',      100),
(    'B',      'X',      150)

Select [State], Item, Sum(Amount) Amount
From @Data
Group by [State], Item with Rollup

score 0 · Answer 6 · answered Apr 16 '20 at 11:31

0

I recommend grouping sets. It provides much more control than rollup:

select state, coalesce(item, 'Total') as item,
       sum(amount) as amount
from t
group by grouping sets ( (state, item), (state) );

answered Apr 16 '20 at 11:31

Gordon Linoff

1,242,037
58
646
786

SQL Add total sum row for each property

6 Answers6