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

我有这张表，其中包含三个特定的交易：账单、罚金和折扣。我想计算账单金额（（账单 - 折扣）+罚款）。

| 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

回答

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;