Is there a way to use group by together with a total sum of values in group by?-CodePudding

Very new to SQL and hopefully someone can help me with following concept:

SELECT name, sum(amount) as balance
FROM table
GROUP BY name

If the 'Name' variable contain A, B and C I will of course by the above statement get the balance per A, B and C.

However if want the total balance of all 'Names' I can remove the Group by clause and 'Name' from SELECT but my question is if this can be solved in one script? Basically, can I in some way add a row under 'Name' called Total which gives tha balance of A, B and C. So result should contain A, B, C and Total under 'Name' with respective balance?

Thanks,

CodePudding user response：

SELECT name, sum(amount) as balance
FROM table
GROUP BY name

UNION ALL

SELECT 'Total', sum(amount) as balance
FROM table

This might be able to help you out

CodePudding user response：

Most databases support this functionality as a GROUP BY modifier. The standard functionality uses GROUPING SETS:

SELECT name, sum(amount) as balance
FROM table
GROUP BY GROUPING SETS ( (name), () );

In this example (with one key), you can also use ROLLUP. The exact syntax varies by database.