How to get the sum of a column in SQL Server and display in a single query?

Question

I have this table where I have three particular transactions: Billing, Penalty and Discount. I want to compute the billed amount ((Billing - Discount)+Penalty).

| 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  |

This should be the output

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

What I have tried so far: I managed to get the billed amount for each individual account:

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

I would use conditional aggregation here:

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;