SQL: Calculate Summary and Column Summary

DECLARE @SampleTable TABLE(Name VARCHAR(100), Amount DECIMAL(12,2))INSERT INTO @SampleTableSELECT 'Value 1', 1000 UNION ALLSELECT 'Value 1', 2000 UNION ALLSELECT 'Value 1', 3000 UNION ALLSELECT 'Value 2', 1500 UNION ALLSELECT 'Value 3', 600 UNION ALLSELECT 'Value 3', 1800

Using ROLLUP

SELECT COALESCE(Name,'Total') AS Name, SUM(Amount) AS Amount FROM @SampleTableGROUP BY NameWITH ROLLUP

Using UNION ALL

SELECT Name, SUM(Amount) AS Amount FROM @SampleTableGROUP BY NameUNION ALLSELECT 'Total',SUM(Amount) FROM @SampleTable

Source: SQL Server Curry

Leave a Reply

Your email address will not be published. Required fields are marked *