Sum at end of each row and column : MSSQL

Question

5.00/5 (1 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

SQL

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 5-Dec-14 5:48am

Sanjeev236

Updated 5-Dec-14 5:50am

DamithSL

v2

Add a Solution

Comments

Sanjeev236 5-Dec-14 11:52am

Thanks V2

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2014-12-05T08:02:00

Solution 1

The best way is to use Pivot table:

SQL

--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

;)

Posted 5-Dec-14 8:02am

Maciej Los

Updated 5-Dec-14 8:13am

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 ;)

[no name] 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 ;)

[no name] 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 ;)

[no name] 6-Dec-14 11:12am

so as not to get again an excluded member ;)