Click here to Skip to main content
14,243,950 members
Rate this:
Please Sign up or sign in to vote.
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.
Please help me.
Posted
Updated 5-Dec-14 5:50am
v2
Comments
Sanjeev236 5-Dec-14 11:52am
   
Thanks V2

1 solution

Rate this:
Please Sign up or sign in to vote.

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
Comments
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
   
Answer has been updated ;)
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 ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100