Want to get vat-amounts in invoice table I have three tables:


  tblInvoices: fldInvoiceID, fldInvouceDate, fldInvoiceCustomer
  
  tblInvoiceItems: fldInvoiceID, fldItemID, fldItemQty, fldItemProdID, fldVatID
  
  tblVatRates: fldVatRateID, fldVatRate


with the following records:


1, 0;
2, 6;
3, 12;
4, 21;

Now I want to print at the bottom of my invoice a list of total vat-amounts per fldVatRate.
Example:
0 % - 12.00 €
6 % - 25.65 €
12 % - 0.00 €
21 % - 5.19 €
I've try a lot of t-sql statements but never get the real thing. The last t-sql give me the desired outpu BUT the 12% is not mentionde while it is 0. How can I get the wanted output ?
The sql untel now:

SELECT [HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblInvoiceDetail.fldItemQty,
   [HJ-Admin].tblInvoiceDetail.fldItemUnitPrice, [HJ-Admin].tblInvoiceDetail.fldLineTotal, 
   [HJ-Admin].tblVatRates.fldVatRate, [HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100 AS fldVatAmount,
   [HJ-Admin].tblInvoiceDetail.fldVatRateID


FROM   [HJ-Admin].tblVatRates LEFT OUTER JOIN
       [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID

Result:


As you can see the Vat-rate 6% (ID = 2) is mentioned twice while it shoulde be once with the total of the two records (= 1.725000). The NULL-values are just fine to see the amount is NULL.


I would like an output like this:
It should be fairly simple then:

SELECT 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates 
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate


or with a total rows as well:

SELECT 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates 
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
WITH ROLLUP


Or with the grouping that you are after (only an overall total row, no totals per grouping level):

SELECT 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates 
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
GROUP BY GROUPING SETS(
        ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
        , ()
    )


With InvoiceID:

SELECT 
    [HJ-Admin].tblVatRates.fldVatID
    , [HJ-Admin].tblVatRates.fldVatRate
    , SUM([HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100) AS fldVatTotalAmount
FROM [HJ-Admin].tblVatRates 
LEFT OUTER JOIN [HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
WHERE [HJ-Admin].tblInvoiceDetail.fldInvoiceID = @intInvoiceID
GROUP BY GROUPING SETS(
            ([HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblVatRates.fldVatRate)
            , ()
        )


Try to put the InvoiceID into the where, will be more performant.

If you want to also include all non matching rows from the tblVatRate table:

WITH Inv AS (
    SELECT 
        r.fldVatID
        , r.fldVatRate
        , SUM(i.fldLineTotal * r.fldVatRate / 100) AS fldVatTotalAmount
    FROM [HJ-Admin].tblVatRates r
    INNER JOIN [HJ-Admin].tblInvoiceDetail i ON r.fldVatID = i.fldVatRateID
    WHERE i.fldInvoiceID = @intInvoiceID
    GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate)
                , ()
            )
)
SELECT ISNULL(i.fldVatID,@intInvoiceID) AS 'fldVatRateID', r.fldVatRate, SUM(ISNULL(i.fldVatTotalAmount,0)) AS 'fldTotalVat'
FROM [HJ-Admin].tblVatRates r
LEFT OUTER JOIN Inv i ON r.fldVatRate = i.fldVatRate
GROUP BY GROUPING SETS(
                (r.fldVatID, r.fldVatRate, i.fldVatID)
                , ()
            )