How to use SUM function in SQL with dynamic columns

Question

I have arranged a result with Dynamic PIVOT into Temp Table, which looks like:

--------------------------------------------------------
| City        | 2018-07-14 | 2018-07-15  |  2018-07-16 |
--------------------------------------------------------
| Satara      |     3      |      9      |      1      |
| Maharashtra |     0      |      4      |      1      |
| Ghatkopar   |     10     |      1      |      1      |
--------------------------------------------------------

Expected output :

----------------------------------------------------------------------
| City        |  2018-07-14 | 2018-07-15 | 2018-07-16 |    Total     |
---------------------------------------------------------------------
| Satara      |   3         |     9      |      1     |      13      |
| Maharashtra |   0         |     4      |      1     |      5       |
| Ghatkopar   |   10        |     1      |      1     |      12      |
----------------------------------------------------------------------
 NULL         |   13        |     14    |      3      |      30      |
--------------------------------------------------------------------

That last three columns is date which is dynamic Today's Date - 3 Days

You'd probably be better aggregating for your column `Total` before you `PIVOT`, and then pivoting that as well. Otherwise, the simple is it's simple maths: `[2018-07-14]+[2018-07-15]+[2018-07-16] AS Total`. I can't give you more detail than that, as I we don't have your SQL, just some data. — Thom A, Jul 16 '18 at 13:05
One way to append the vertical line is to UNION on a summation query with the same pivot as the detail. — Ross Bush, Jul 16 '18 at 13:08
But that date is dynamic -- it will change tommarow from 17 July to 15 July — Prashant Pimpale, Jul 16 '18 at 13:08
@PrashantPimpale - You can use date range parameters to make your query more flexible. — Ross Bush, Jul 16 '18 at 13:10
Refer this answer https://stackoverflow.com/a/41610367/3752496 — Shakeer Mirza, Jul 16 '18 at 13:12
Yes, and as the data the dynamic, that's why I'm saying aggregate for your column `Total`, and then `PIVOT`. Not `PIVOT` and aggregate. — Thom A, Jul 16 '18 at 13:12
Show your full SQL and we can help out here. This definitely 100% needs to happen before you pivot and since we have no idea what that looks like we can't help out more. I would suggest a UNION and then PIVOT. — JNevill, Jul 16 '18 at 13:20

Max Szczurek · Accepted Answer · 2018-07-16T13:29:49.703

You can use GROUP BY ROLLUP to get your vertical totals. In this example, I'm calculating Total as a computed column, but you could aggregate before the pivot instead.

DECLARE @tbl TABLE (City VARCHAR(25), [2018-07-14] int, [2018-07-15] int, 
    [2018-07-16] int, Total AS [2018-07-14] + [2018-07-15] + [2018-07-16])

INSERT INTO @tbl VALUES ('Satara', 3, 9, 1)
INSERT INTO @tbl VALUES ('Maharashtra', 0, 4, 1)
INSERT INTO @tbl VALUES ('Ghatkopar', 10, 1, 1)

SELECT City, SUM([2018-07-14]) AS [2018-07-14], 
    SUM([2018-07-15]) AS [2018-07-15], 
    SUM([2018-07-16]) AS [2018-07-16],
    SUM(Total) AS Total
FROM @tbl
GROUP BY ROLLUP (City);

Returns:

City        2018-07-14  2018-07-15  2018-07-16  Total
Ghatkopar   10          1           1           12
Maharashtra 0           4           1           5
Satara      3           9           1           13
NULL        13          14          3           30

If you're worried about dynamic column headers, you can change them programmatically with dynamic sql:

DECLARE @D1 VARCHAR(10) = FORMAT(GETDATE() - 2, 'yyyy-MM-dd')
DECLARE @D2 VARCHAR(10) = FORMAT(GETDATE() - 1, 'yyyy-MM-dd')
DECLARE @D3 VARCHAR(10) = FORMAT(GETDATE(), 'yyyy-MM-dd')

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT City, SUM([' + @D1 + ']) AS [' + @D1 + '], 
    SUM([' + @D2 + ']) AS [' + @D2 + '], 
    SUM([' + @D3 + ']) AS [' + @D3 + '],
    SUM([' + @D1 + '] + [' + @D2 + '] + [' + @D3 + ']) AS Total
FROM tbl
GROUP BY ROLLUP (City);'

EXEC sp_executesql @SQL

Thanks for the response! As I test with ROLLUP with single column its working fine but I have multiple columns in select like contact_number so by group by them it produces an incorrect result — Prashant Pimpale, Jul 16 '18 at 14:03
@PrashantPimpale If you want to rollup by multiple columns, GROUP BY ROLLUP ((City, ContactNumber)); Make sure you include the 2nd set of parentheses, though; that'll combine them as a grouping set. If you do GROUP BY ROLLUP (City, ContactNumber), it'll group by City as a grouping set, and then ContactNumber as a second grouping set. — Max Szczurek, Jul 16 '18 at 14:58
there are 2 more columns including city like `ContactNumber`, `Name` so If I use group by then the data is getting duplicated — Prashant Pimpale, Jul 17 '18 at 09:53
I would be very helpful if you could help me out on [this](https://stackoverflow.com/q/53372997/7124761) — Prashant Pimpale, Nov 20 '18 at 05:54

How to use SUM function in SQL with dynamic columns

1 Answers1