SQL Server query to get the total for each row

Question

I have two tables InvoiceLine and Discount:

I need to get a result set which includes invoice Line items with the total (SUM) of discounts in the discount table like below:

How can I achieve this using only one query?

Answer 1

DECLARE @InvoiceLine TABLE
    (
      InvoiceHeaderID INT ,
      InvoiceLineNo INT ,
      ProductCode VARCHAR(5) ,
      Price MONEY
    );

INSERT  INTO @InvoiceLine
VALUES  ( 1, 1, 'AB001', 1200 ),
        ( 2, 1, 'AC002', 1525 );


DECLARE @Discount TABLE
    (
      InvoiceHeaderID INT ,
      InvoiceLineNo INT ,
      DiscountCategory VARCHAR(10) ,
      discountValue MONEY
    );
INSERT  INTO @Discount
VALUES  ( 1, 1, 'SalesDisc', 120 ),
        ( 1, 1, 'FixedOffer', 100 ),
        ( 2, 1, 'SalesDisc', 152.50 );


SELECT  l.InvoiceHeaderID ,
        l.InvoiceLineNo ,
        l.ProductCode ,
        l.Price ,
        ISNULL(SUM(d.discountValue),0) [TotalDiscount]
FROM    @InvoiceLine l
        LEFT JOIN @Discount d ON d.InvoiceHeaderID = l.InvoiceHeaderID
                                 AND d.InvoiceLineNo = l.InvoiceLineNo
GROUP BY l.InvoiceHeaderID ,
        l.InvoiceLineNo ,
        l.ProductCode ,
        l.Price; 

Result