如何在SQL Server中获取列的总和并在单个查询中显示？

Question

我有这张表，我有三个特定的交易:账单，罚款和折扣。我想计算账单金额((账单-折扣)+罚金)。

| CustNumber | Particular | Amount |
| 1001       | Billing    | 170.00 |
| 1001       | Penalty    | 17.00  |
| 1001       | Discount   | 8.50   |
| 1002       | Billing    | 250.00 |
| 1002       | Penalty    | 25.00  |
| 1002       | Discount   | 12.50  |
| 1003       | Billing    | 500.00 |
| 1003       | Penalty    | 50.00  |
| 1003       | Discount   | 25.00  |
| 1004       | Billing    | 200.00 |
| 1004       | Penalty    | 20.00  |
| 1004       | Discount   | 10.00  |

这应该是输出

| CustNumber | BilledAmount |
| 1001       | 178.50       |
| 1002       | 262.50       |
| 1003       | 525.00       |
| 1004       | 210.00       |

到目前为止，我尝试的是:我设法获得了每个帐户的账单金额：

ALTER PROCEDURE [dbo].[spComputeBilledAmount]
    @CustId int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @billing decimal(18,2),
            @penalty decimal(18,2),
            @discount decimal(18,2)

    SELECT 
        @billing = COALESCE(SUM(CASE WHEN particulars = 'Billing' THEN [Amount] ELSE 0 END), 0),
        @penalty = COALESCE(SUM(CASE WHEN particulars = 'Penalty' THEN [Amount] ELSE 0 END), 0),
        @discount = COALESCE(SUM(CASE WHEN particulars = 'Discount' THEN [Amount] ELSE 0 END), 0)
    FROM
        [Transactions]
    WHERE 
        [ConcessionaireId] = @CustId
                            
    SELECT ((@billing-@discount)+ @penalty)  AS 'BilledAmount'
END

Answer 1

我将在这里使用条件聚合：

SELECT
    CustNumber,
    MAX(CASE WHEN Particular = 'Billing' THEN Amount ELSE 0 END) +
    MAX(CASE WHEN Particular = 'Penalty' THEN Amount ELSE 0 END) -
    MAX(CASE WHEN Particular = 'Discount' THEN Amount ELSE 0 END) AS BilledAmount
FROM [Transactions]
GROUP BY
    CustNumber;

Answer 2

您的存储过程一次只处理一个CustNumber。这是不切实际的，你不需要存储过程来做这件事。

SELECT t.CustNumber,
       SUM 
       ( 
           CASE Particular
           WHEN 'Billing'  THEN +t.Amount 
           WHEN 'Penalty'  THEN +t.Amount 
           WHEN 'Discount' THEN -t.Amount 
           END
       ) AS BilledAmount 
FROM   [Transactions] t
GROUP BY t.CustNumber