基于“一列的SUM除以另一列的SUM"的SQL Pivot查询. [英] SQL Pivot query based on "SUM of a column divided by SUM of another column"
问题描述
我正在尝试找出如何根据特定列的SUM除以另一列的SUM来轮换数据."到目前为止,这是我到达的位置,但是PIVOT查询给我错误: /p>
I am trying to figure out how to pivot my data based on the SUM of a particular column divided by the SUM of another Column". Here is where I've reached so far but the PIVOT query is giving me errors:
USE MyDatabase
SELECT [PropertyCode],
[Market],
[JANUARY 2015],
[FEBRUARY 2015],
[MARCH 2015]
FROM (SELECT [PropertyCode],
[Market],
[MTH],
[GN],
[PkgRev]
FROM HOLDINGS
) a
PIVOT (Sum([PkgRev])/Sum([GN])
FOR [MTH] IN ([JANUARY 2015],
[FEBRUARY 2015],
[MARCH 2015])) as pvttable
ORDER BY [PROPERTYCODE]
上面查询中的
HOLDINGS是一个VIEW表,而PkgRev和GN是该VIEW表中的列.在执行PkgRev列的SUM除以GN列的SUM之后,我需要数据透视查询返回值.
HOLDINGS in the query above is a VIEW table and PkgRev and GN are columns in that VIEW table. I need my Pivot query to return values after performing a SUM of the PkgRev column DIVIDED by the SUM of the GN column.
以下是数据的示例(来自HOLDINGS视图表):
Here is a sample of the data (from the HOLDINGS view table):
PropertyCode ID GN MTH Pkg Rev Market
A 1 10 Jan-15 95,652 United Kingdom
A 3 20 Jan-15 95,652 United Kingdom
A 12 24 Feb-15 81,610 France
A 15 14 Feb-15 47,606 France
B 16 18 Jan-15 38,153 France
B 20 28 Feb-15 69,391 United Kingdom
B 22 32 Jan-15 107,990 Germany
B 25 28 Feb-15 76,696 United Kingdom
C 26 4 Jan-15 18,961 Germany
C 27 26 Jan-15 56,522 South Africa
C 29 20 Feb-15 27,826 India
C 31 18 Jan-15 33,661 France
C 32 14 Jan-15 26,039 Germany
预期输出如下:
PropertyCode Market Jan-15 Feb-15
A France 0 3,400
A United Kingdom 6,377 0
B France 2,120 0
B Germany 3,375 0
B United Kingdom 0 2,609
C France 1,870 0
C Germany 1,607 0
C India 0 1,391
C South Africa 2,174 0
推荐答案
旋转时,您应该记住:
1:aggregation and spreading elements cannot directly be results of expressions
2:分组元素是identified by elimination
When pivoting you should remember that:
1: aggregation and spreading elements cannot directly be results of expressions
2: grouping element are identified by elimination
我建议您重写如下:
DECLARE @t TABLE
(
ProperTyCode CHAR(1) ,
ID INT ,
GN int ,
MTH NVARCHAR(10) ,
Pkg int ,
Market NVARCHAR(20)
)
INSERT INTO @t
VALUES ( 'A', 1, 10, 'Jan-15', 95652, 'United Kingdom' ),
( 'A', 3, 20, 'Jan-15', 95652, 'United Kingdom' ),
( 'A', 12, 24, 'Feb-15', 81610, 'France' ),
( 'A', 15, 14, 'Feb-15', 47606, 'France' ),
( 'B', 16, 18, 'Jan-15', 38153, 'France' ),
( 'B', 20, 28, 'Feb-15', 69391, 'United Kingdom' ),
( 'B', 22, 32, 'Jan-15', 107990, 'Germany' ),
( 'B', 25, 28, 'Feb-15', 76696, 'United Kingdom' ),
( 'C', 26, 4, 'Jan-15', 18961, 'Germany' ),
( 'C', 27, 26, 'Jan-15', 56522, 'South Africa' ),
( 'C', 29, 20, 'Feb-15', 27826, 'India' ),
( 'C', 31, 18, 'Jan-15', 33661, 'France' ),
( 'C', 32, 14, 'Jan-15', 26039, 'Germany' );
WITH cte
AS ( SELECT [PropertyCode] ,
[Market] ,
[MTH] ,
CAST(ROUND(SUM([Pkg]) / CAST(SUM([GN]) AS DECIMAL(20, 5)), 0) AS INT) AS [GR]
FROM @t
GROUP BY [PropertyCode] ,
[Market] ,
[MTH]
)
SELECT [PropertyCode] ,
[Market] ,
ISNULL([Jan-15], 0) AS [Jan-15],
ISNULL([Feb-15], 0) AS [Feb-15]
FROM cte PIVOT ( MAX([GR]) FOR [MTH] IN ( [Jan-15], [Feb-15] ) ) AS pvttable
ORDER BY [PROPERTYCODE]
输出:
PropertyCode Market Jan-15 Feb-15
A France 0 3400
A United Kingdom 6377 0
B France 2120 0
B Germany 3375 0
B United Kingdom 0 2609
C France 1870 0
C Germany 2500 0
C India 0 1391
C South Africa 2174 0
这篇关于基于“一列的SUM除以另一列的SUM"的SQL Pivot查询.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!