每行和每列末尾的总和:MSSQL [英] Sum at end of each row and column : MSSQL
问题描述
我想在结尾显示列和行的总和。对于EG
必需输出:
<前lang =文本> 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
输入表格为
<前lang =text> Date1位置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
< span class =code-keyword> DECLARE @cols AS NVARCHAR (MAX),
@ query AS < span class =code-keyword> 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 = ' 选择date1为DATE,' + @ c ols + ' ,sale2 as TOTAL from(select date1,sale1,sale2,work_location from temp)x
pivot
(work_location的最大值(sale1)(' + @cols + ' ))按日期排序1'
执行( @ query )
drop table temp
我尝试了以下查询并能够生成TOTAL_ROW但不是TOTAL_COL。
请帮助我。
最好的方法是使用数据透视表:
- DROP TABLE #tmp
CREATE 表 #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'
<跨班=code-keyword> 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 < span class =code-keyword> TABLE #tmp
结果:
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;)
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.
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;)
这篇关于每行和每列末尾的总和:MSSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!