14,243,950 members
Rate this:
See more:
Hi,
I want to show sum of column and row at end. For E.G

Required Output :
```DATE	        GURGAON	INDORE	TOTAL_ROW
2014/12/01	1889.93		1889.93
2014/12/02	1539.94	1299.96	2839.9
2014/12/03	2429.9	1299.92	3729.82
2014/12/04	499.98	749.94	1249.92
TOTAL_COL       6359.75 3349.82 9709.57```

Input Table as
```Date1	       location	Sale1
2014/12/01	GURGAON	1889.93
2014/12/02	GURGAON	1539.94
2014/12/02	INDORE	1299.96
2014/12/03	INDORE	1299.92
2014/12/03	GURGAON	2429.9
2014/12/04	GURGAON	499.98
2014/12/04	INDORE	749.94```

```DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.work_location) FROM temp c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT date1 as DATE, ' + @cols + ',sale2 as TOTAL from (select date1,sale1,sale2,work_location from temp) x
pivot
(max(sale1)for work_location in (' + @cols + ')) p  order by date1'
execute(@query)
drop table temp```

I tried following query and able to generate TOTAL_ROW But not TOTAL_COL.
Posted
Updated 5-Dec-14 5:50am
v2
Sanjeev236 5-Dec-14 11:52am

Thanks V2

Rate this:

## Solution 1

The best way is to use Pivot table:
```--DROP TABLE #tmp

CREATE TABLE #tmp (Date1 DATE, location	VARCHAR(30), Sale1 DECIMAL(8,2))
INSERT INTO #tmp (Date1, location, Sale1)
VALUES('2014/12/01', 'GURGAON', 1889.93),
('2014/12/02', 'GURGAON', 1539.94),
('2014/12/02', 'INDORE', 1299.96),
('2014/12/03', 'INDORE', 1299.92),
('2014/12/03', 'GURGAON', 2429.9),
('2014/12/04', 'GURGAON', 499.98),
('2014/12/04', 'INDORE', 749.94)

DECLARE @SumG DECIMAL(8,2)
SELECT @SumG = SUM(Sale1) FROM #tmp WHERE location = 'GURGAON'
DECLARE @SumI DECIMAL(8,2)
SELECT @SumI  = SUM(Sale1) FROM #tmp WHERE location = 'INDORE'

SELECT DATE1, GURGAON, INDORE, COALESCE(GURGAON,0) + COALESCE(INDORE,0) AS SumOfRow
FROM (
SELECT *
FROM #tmp
) AS DT
PIVOT(SUM(Sale1) FOR location IN (GURGAON, INDORE )) AS PT
UNION ALL
SELECT NULL, @SumG AS GURGAON, @SumI AS INDORE, @SumG + @SumI

DROP TABLE #tmp```

Result:
```DATE1	GURGAON	INDORE	SumOfRow
2014-12-01	1889.93	NULL	1889.93
2014-12-02	1539.94	1299.96	2839.90
2014-12-03	2429.90	1299.92	3729.82
2014-12-04	499.98	749.94	1249.92
NULL	    6359.75	3349.82	9709.57```
;)
v3
Marcin Kozub 5-Dec-14 14:06pm

Yeah, pivot is the answer, 5ed :)
Sanjeev236 5-Dec-14 14:09pm

Friend, i know pivot is the answer and same i mentioned in my query. But i couldn't figure out the best way. So i posted the question for experts advice.
Thanks :)
Marcin Kozub 5-Dec-14 14:13pm

Sry, I missed 'pivot' word in your query ;)
Sanjeev236 5-Dec-14 14:07pm

Oh mate... you left me where i started. But i like your solution and advice. Soon i'll try to post exact solution fro Sum of col. Loving it brother. :)
Maciej Los 5-Dec-14 14:14pm

Sanjeev236 5-Dec-14 14:11pm

Maciej Los: Apart from Pivot,can you tell me how to use smiley on code project..like you did? :-)
Maciej Los 5-Dec-14 14:15pm

Just type ;)
King Fisher 6-Dec-14 2:34am

wow. my 5 ;)
Maciej Los 6-Dec-14 2:47am

Thank you, King ;)
0x01AA 6-Dec-14 9:53am

I hate pivot, but I like your solution. My 5.
Bruno
Maciej Los 6-Dec-14 10:18am

Thank you, Bruno. Welcome back ;)
0x01AA 6-Dec-14 10:41am

Back thanks to you. I will be quiet ;)
Maciej Los 6-Dec-14 11:04am

Quiet? Why?
CodeProject community needs every single member ;)
0x01AA 6-Dec-14 11:12am

so as not to get again an excluded member ;)