SQL Add total sum row for each property

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

Answer

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