Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add a summary row with totals

I know this sounds crazy and probably should not be done this way but I need something like this - I have a records from SELECT [Type], [Total Sales] From Before

I want to add an extra row at the end to show the SUM at the end of the table (After). Could this be done?

enter image description here

like image 428
user2103670 Avatar asked Jul 29 '13 21:07

user2103670


People also ask

How do I add a total row and display totals in Excel?

Go to Table Tools > Design, and select the check box for Total Row. The Total Row is inserted at the bottom of your table. Note: If you apply formulas to a total row, then toggle the total row off and on, Excel will remember your formulas.

How do I add a total row in SQL?

In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set.

How do you sum summary in Excel?

Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

How do you add a total row to a data sheet?

Add a Totals rowOn the Home tab, in the Records group, click Totals. For each cell in the Total row where you want a total to appear, click in the cell and select the kind of total you want.


1 Answers

If you are on SQL Server 2008 or later version, you can use the ROLLUP() GROUP BY function:

SELECT   Type = ISNULL(Type, 'Total'),   TotalSales = SUM(TotalSales) FROM atable GROUP BY ROLLUP(Type) ; 

This assumes that the Type column cannot have NULLs and so the NULL in this query would indicate the rollup row, the one with the grand total. However, if the Type column can have NULLs of its own, the more proper type of accounting for the total row would be like in @Declan_K's answer, i.e. using the GROUPING() function:

SELECT   Type = CASE GROUPING(Type) WHEN 1 THEN 'Total' ELSE Type END,   TotalSales = SUM(TotalSales) FROM atable GROUP BY ROLLUP(Type) ; 
like image 50
Andriy M Avatar answered Oct 17 '22 04:10

Andriy M