如何在 SQL Server 中按列和按行求和? [英] How to sum column wise and row-wise in SQL Server?

问题描述

请看下面

我要找的是

即总计应该在行和列级别

我的尝试

declare @t table(aggrementid varchar(20), bom_pos int, bucket int null, paymentstatus varchar(50))

insert into @t 
    select '1', 3000, null, null  
    union all 
    select '2', 3000, 0, 'Non-Delinquient' 
    union all
    select '3', 4000, 0, 'Non-Delinquient' 
    union all 
    select '4', 5000, 0, 'Non-Delinquient' 
    union all 
    select '5', 7000, 0, 'NPA' 
    union all 
    select '6', 8000, 1, 'NPA'

Select 
    bucket,
    [Non-Delinquient], [NPA], [RollBack], [RollForward],
    [Stabilized], [Normalized], [PaymentStatusY],
    Total = iif([Non-Delinquient] is null, 0, [Non-Delinquient]) + 
        iif([NPA] is null, 0, [NPA]) + 
        iif([RollBack] is null, 0, [RollBack]) + 
        iif([RollForward] is null, 0, [RollForward]) + 
        iif([Stabilized] is null, 0, [Stabilized]) + 
        iif([Normalized] is null, 0, [Normalized]) + 
        iif([PaymentStatusY] is null, 0, [PaymentStatusY])
From
    (Select 
         --aggrementid, 
         bom_pos,
         bucket, paymentstatus      
     From 
         @t) as PivotSource
PIVOT
    (sum(bom_pos) FOR paymentstatus IN ([Non-Delinquient], [NPA],[RollBack],[RollForward],[Stabilized],[Normalized],[PaymentStatusY])
    ) as Pvt
Where 
    bucket is not null

返回这个结果:

另外，是否可以通过使用 Rollup 函数等来实现?

Also, can the same can be achieved by using Rollup function etc?

推荐答案

我认为你可以使用 GROUP BY 和 GROUPING SETS

I think you can use GROUP BY with GROUPING SETS

declare @t table(aggrementid varchar(20), bom_pos int, bucket int null, paymentstatus varchar(50))

insert into @t 
    select '1', 3000, null, null  
    union all 
    select '2', 3000, 0, 'Non-Delinquient' 
    union all
    select '3', 4000, 0, 'Non-Delinquient' 
    union all 
    select '4', 5000, 0, 'Non-Delinquient' 
    union all 
    select '5', 7000, 0, 'NPA' 
    union all 
    select '6', 8000, 1, 'NPA'

Select 
    isnull(CAST(bucket AS varchar(10)),'Total') bucket,
    SUM([Non-Delinquient]) [Non-Delinquient],
    SUM([NPA]) [NPA],
    SUM([RollBack]) [RollBack],
    SUM([RollForward]) [RollForward],
    SUM([Stabilized]) [Stabilized],
    SUM([Normalized]) [Normalized],
    SUM([PaymentStatusY]) [PaymentStatusY],
    SUM(isnull([Non-Delinquient],0) + isnull([NPA],0) + isnull([RollBack],0) + isnull([RollForward],0) + isnull([Stabilized],0) + isnull([Normalized],0) + isnull([PaymentStatusY],0)) Total
From
    (Select 
         --aggrementid, 
         bom_pos,
         bucket, paymentstatus      
     From 
         @t) as PivotSource
PIVOT
    (sum(bom_pos) FOR paymentstatus IN ([Non-Delinquient], [NPA],[RollBack],[RollForward],[Stabilized],[Normalized],[PaymentStatusY])
    ) as Pvt
Where 
    bucket is not null
GROUP BY GROUPING SETS(
                        (bucket),
                        ()
                      )

另请参阅以下示例以了解其工作原理

See also the following example for understanding how it works

SELECT
  CASE GROUPING_ID(GroupID,SubgroupID)
    WHEN 3 THEN 'Total'
    WHEN 1 THEN 'Subtotal by Group'
    WHEN 0 THEN ''
  END RowTitle,

  GroupID,
  SubgroupID,
  SUM(Value) Value
FROM
  (
    SELECT 1 GroupID,1 SubgroupID,10 Value
    UNION ALL SELECT 1 GroupID,2 SubgroupID,5 Value
    UNION ALL SELECT 1 GroupID,3 SubgroupID,5 Value
    UNION ALL SELECT 2 GroupID,1 SubgroupID,11 Value
    UNION ALL SELECT 2 GroupID,2 SubgroupID,12 Value
  ) q
GROUP BY GROUPING SETS(
            (GroupID,SubgroupID),
            (GroupID),
            ()
          )

您也可以使用 SUM 和 CASE 代替 PIVOT.对我来说更清楚

You also can use SUM with CASE instead PIVOT. For me it's more clearly

SELECT
  bucket,
  SUM(CASE WHEN paymentstatus='Non-Delinquient' THEN bom_pos END) [Non-Delinquient],
  SUM(CASE WHEN paymentstatus='NPA' THEN bom_pos END) [NPA],
  SUM(CASE WHEN paymentstatus='RollBack' THEN bom_pos END) [RollBack],
  SUM(CASE WHEN paymentstatus='RollForward' THEN bom_pos END) [RollForward],
  SUM(CASE WHEN paymentstatus='Stabilized' THEN bom_pos END) [Stabilized],
  SUM(CASE WHEN paymentstatus='Normalized' THEN bom_pos END) [Normalized],
  SUM(CASE WHEN paymentstatus='PaymentStatusY' THEN bom_pos END) [PaymentStatusY],
  SUM(bom_pos) Total
FROM @t
WHERE paymentstatus IN('Non-Delinquient', 'NPA','RollBack','RollForward','Stabilized','Normalized','PaymentStatusY')
GROUP BY GROUPING SETS(
                        (bucket),
                        ()
                      )

这篇关于如何在 SQL Server 中按列和按行求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！