时间戳

首页 > 解决方案 > TSQL Pivot Sum 函数结果不正确

sql - TSQL Pivot Sum 函数结果不正确

问题描述

我在 SQL 2012 中有一个表，其中有 282 行（##OOS2.

如果我使用 Excel 对表中的“OOS_Duration”字段或该字段的总和进行求和，则 OOS_Duration 列的总数为 1474.457，但如果我尝试对管辖权进行透视，则 SUM 函数会得出错误的答案 1089.804 .

我尝试将 OOS_Duration 从 FLOAT 转换为 Decimal 和/或 Numeric 但仍然得到错误的答案。

有没有人对 PIVOT SUM 函数有任何经验，但它没有返回正确的结果？

下面是我的代码

      select  
        piv.monthname as Month
        ,piv.Monthnum
        ,piv.North

from (
        select 
                  [Monthname], Monthnum,Jurisdiction, OOS_Duration      
        from ##OOS2
        group by  [Monthname], Monthnum,Jurisdiction, OOS_Duration  

union
        select  [monthname],monthnum, 'ALL', OOS_Duration   
        from ##OOS
        group by [Monthname],[Monthnum], Jurisdiction, OOS_Duration 

     ) as SRC1
Pivot
        (
        sum(OOS_Duration)
        for     Jurisdiction in ([North],[South],[East],[West],[All])
        )piv
        order by Monthnum;
`````````````````````````````````````````````````````````````````````````````````
here is my table
`````````````````````````````````````````
monthname,Monthnum,Jurisdiction,OOS_Duration
Jan,1,North,3.562222222
Jan,1,North,0.818333333
Jan,1,North,11.69055556
Jan,1,North,0.161111111
Jan,1,North,12
Jan,1,North,12
Jan,1,North,2.474166667
Jan,1,North,0.336944444
Jan,1,North,1.144722222
Jan,1,North,11.18666667
Jan,1,North,12
Jan,1,North,0.006388889
Jan,1,North,2.597222222
Jan,1,North,11.83166667
Jan,1,North,12
Jan,1,North,11.94833333
Jan,1,North,11.94222222
Jan,1,North,1.131666667
Jan,1,North,10.44777778
Jan,1,North,9.406111111
Jan,1,North,0.948055556
Jan,1,North,11.95388889
Jan,1,North,11.94944444
Jan,1,North,0.444444444
Jan,1,North,0.929166667
Jan,1,North,0.986666667
Jan,1,North,0.013333333
Jan,1,North,5.12
Jan,1,North,12
Jan,1,North,0.229166667
Jan,1,North,11.97888889
Jan,1,North,1.72
Jan,1,North,0.490277778
Jan,1,North,1.363055556
Jan,1,North,0.441388889
Jan,1,North,0.935555556
Jan,1,North,11.94527778
Jan,1,North,11.25305556
Jan,1,North,12
Jan,1,North,12
Jan,1,North,12
Jan,1,North,12
Jan,1,North,8.945555556
Jan,1,North,0.131944444
Jan,1,North,1.9825
Jan,1,North,0.359444444
Jan,1,North,12
Jan,1,North,2.915833333
Jan,1,North,2.828611111
Jan,1,North,1.098888889
Jan,1,North,1.851944444
Jan,1,North,2.988888889
Jan,1,North,11.90222222
Jan,1,North,6.6025
Jan,1,North,11.51888889
Jan,1,North,2.836666667
Jan,1,North,0.1
Jan,1,North,1.265277778
Jan,1,North,0.590555556
Jan,1,North,6.250277778
Jan,1,North,9.981944444
Jan,1,North,12
Jan,1,North,5.642222222
Jan,1,North,12
Jan,1,North,11.99638889
Jan,1,North,6.336388889
Jan,1,North,12
Jan,1,North,0.645833333
Jan,1,North,11.91888889
Jan,1,North,0.866944444
Jan,1,North,11.89277778
Jan,1,North,4.321666667
Jan,1,North,11.89083333
Jan,1,North,0.219722222
Jan,1,North,9.392222222
Jan,1,North,5.556666667
Jan,1,North,0.705555556
Jan,1,North,0.631666667
Jan,1,North,0.213888889
Jan,1,North,11.91638889
Jan,1,North,0.107777778
Jan,1,North,0.037777778
Jan,1,North,11.89027778
Jan,1,North,0.607777778
Jan,1,North,12
Jan,1,North,11.08388889
Jan,1,North,0.313611111
Jan,1,North,7.637777778
Jan,1,North,0.565555556
Jan,1,North,11.80361111
Jan,1,North,1.304166667
Jan,1,North,0.05
Jan,1,North,12
Jan,1,North,12
Jan,1,North,1.260833333
Jan,1,North,0.977777778
Jan,1,North,2.454444444
Jan,1,North,2.236666667
Jan,1,North,0.058055556
Jan,1,North,9.750277778
Jan,1,North,11.96166667
Jan,1,North,11.79194444
Jan,1,North,2.930277778
Jan,1,North,10.61166667
Jan,1,North,0.831666667
Jan,1,North,0.951944444
Jan,1,North,8.951666667
Jan,1,North,12
Jan,1,North,10.95777778
Jan,1,North,11.58083333
Jan,1,North,0.041388889
Jan,1,North,11.90638889
Jan,1,North,1.172777778
Jan,1,North,1.926111111
Jan,1,North,0.016388889
Jan,1,North,0.660833333
Jan,1,North,1.252777778
Jan,1,North,0.859444444
Jan,1,North,0.034166667
Jan,1,North,0.293611111
Jan,1,North,1.003888889
Jan,1,North,10.49611111
Jan,1,North,0.329722222
Jan,1,North,9.791666667
Jan,1,North,0.363611111
Jan,1,North,3.419166667
Jan,1,North,11.96333333
Jan,1,North,0.476944444
Jan,1,North,1.069444444
Jan,1,North,12
Jan,1,North,1.723611111
Jan,1,North,1.995833333
Jan,1,North,11.58472222
Jan,1,North,0.357777778
Jan,1,North,1.084444444
Jan,1,North,0.392222222
Jan,1,North,12
Jan,1,North,0.361944444
Jan,1,North,0.338611111
Jan,1,North,0.353333333
Jan,1,North,0.961944444
Jan,1,North,1.723888889
Jan,1,North,0.423611111
Jan,1,North,11.31527778
Jan,1,North,11.24305556
Jan,1,North,0.344166667
Jan,1,North,2.221111111
Jan,1,North,0.273611111
Jan,1,North,0.431944444
Jan,1,North,0.759166667
Jan,1,North,0.478888889
Jan,1,North,0.009444444
Jan,1,North,11.97333333
Jan,1,North,12
Jan,1,North,12
Jan,1,North,0.563055556
Jan,1,North,11.885
Jan,1,North,2.246388889
Jan,1,North,12
Jan,1,North,2.060833333
Jan,1,North,12
Jan,1,North,0.512777778
Jan,1,North,11.95111111
Jan,1,North,1.288055556
Jan,1,North,11.34833333
Jan,1,North,1.189722222
Jan,1,North,0.589444444
Jan,1,North,0.4625
Jan,1,North,2.039722222
Jan,1,North,11.95194444
Jan,1,North,2.154444444
Jan,1,North,12
Jan,1,North,9.898333333
Jan,1,North,0.926111111
Jan,1,North,5.916666667
Jan,1,North,5.187777778
Jan,1,North,2.963888889
Jan,1,North,0.340277778
Jan,1,North,0.903888889
Jan,1,North,3.614722222
Jan,1,North,0.935833333
Jan,1,North,3.667222222
Jan,1,North,0.011111111
Jan,1,North,0.338611111
Jan,1,North,10.13444444
Jan,1,North,12
Jan,1,North,11.96666667
Jan,1,North,0.761944444
Jan,1,North,9.816944444
Jan,1,North,12
Jan,1,North,0.313611111
Jan,1,North,11.35972222
Jan,1,North,5.331388889
Jan,1,North,11.79555556
Jan,1,North,11.93805556
Jan,1,North,2.33
Jan,1,North,11.97666667
Jan,1,North,0.598333333
Jan,1,North,0.286388889
Jan,1,North,2.299722222
Jan,1,North,12
Jan,1,North,1.908611111
Jan,1,North,2.983888889
Jan,1,North,11.91305556
Jan,1,North,8.766388889
Jan,1,North,11.95833333
Jan,1,North,2.173333333
Jan,1,North,0.678333333
Jan,1,North,1.987222222
Jan,1,North,0.248888889
Jan,1,North,12
Jan,1,North,0.709444444
Jan,1,North,11.81916667
Jan,1,North,2.509166667
Jan,1,North,1.516388889
Jan,1,North,12
Jan,1,North,0.232222222
Jan,1,North,0.8575
Jan,1,North,12
Jan,1,North,0.473333333
Jan,1,North,3.454166667
Jan,1,North,11.43722222
Jan,1,North,1.521944444
Jan,1,North,11.60305556
Jan,1,North,10.57611111
Jan,1,North,8.281111111
Jan,1,North,11.46916667
Jan,1,North,8.160555556
Jan,1,North,1.401388889
Jan,1,North,0.000277778
Jan,1,North,11.86972222
Jan,1,North,3.900555556
Jan,1,North,1.272222222
Jan,1,North,11.8825
Jan,1,North,11.88555556
Jan,1,North,12
Jan,1,North,11.73444444
Jan,1,North,11.9375
Jan,1,North,0.486388889
Jan,1,North,9.448055556
Jan,1,North,0.685277778
Jan,1,North,0.429444444
Jan,1,North,11.74777778
Jan,1,North,0.032222222
Jan,1,North,6.021944444
Jan,1,North,0.8125
Jan,1,North,2.630555556
Jan,1,North,1.733055556
Jan,1,North,0.246944444
Jan,1,North,11.97055556
Jan,1,North,0.140277778
Jan,1,North,0.514722222
Jan,1,North,0.140833333
Jan,1,North,12
Jan,1,North,5.6675
Jan,1,North,0.648611111
Jan,1,North,11.88
Jan,1,North,12
Jan,1,North,10.02944444
Jan,1,North,11.9925
Jan,1,North,0.436388889
Jan,1,North,1.293888889
Jan,1,North,0.3725
Jan,1,North,1.670555556
Jan,1,North,2.568333333
Jan,1,North,0.9975
Jan,1,North,0.738611111
Jan,1,North,10.96111111
Jan,1,North,1.273055556
Jan,1,North,0.034722222
Jan,1,North,0.381388889
Jan,1,North,1.416388889
Jan,1,North,0.079722222
Jan,1,North,0.131388889
Jan,1,North,3.165555556
Jan,1,North,0.014444444
Jan,1,North,0.635555556
Jan,1,North,0.528888889
Jan,1,North,0.642222222
Jan,1,North,0.026944444
Jan,1,North,0.906388889
Jan,1,North,2.115277778

标签： sqltsqlsql-server-2012sumpivot

解决方案

我会推荐条件聚合而不是供应商特定的pivot语法。它更灵活，我发现它更容易遵循。

您没有显示您想要的结果，但我认为这是：

select
    monthname,
    monthnum,
    sum(case when jurisdiction = 'North' then oos_duration else 0 end) North,
    sum(case when jurisdiction = 'South' then oos_duration else 0 end) South,
    sum(case when jurisdiction = 'East'  then oos_duration else 0 end) East,
    sum(case when jurisdiction = 'West'  then oos_duration else 0 end) West,
    sum(case when jurisdiction = 'ALL'   then oos_duration else 0 end) All
from (
    select monthname, monthnum, jurisdiction, oos_duration from ##oos2
    union all
    select monthname, monthnum, 'ALL',        oos_duration from ##oos
) t
group by monthname, monthnum
order by monthnum

推荐阅读