SQL Query with multiple subtotals

Question

Hoping someone can help me out here.

I'm trying to have subtotals in an sql query, but not as another column.

See screenshot with the results I get and the explanation

This is the query I have so far

SELECT 
    ar.Person_Id AS 'Id', 
    pa.Serial_Number, 
    ar.Family_Name + ', '+ar.First_Name AS 'Name', 
    CASE WHEN ar.Line_Type = 'A' THEN 'Activity: '+ar.Item_Name ELSE 'Hotel: '+ar.Item_Name END AS 'Description', 
    CAST(IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2)) AS 'Amount Excl VAT', 
    CAST(IsNull(ar.Old_Amount_Incl_VAT,0) AS DECIMAL(10,2)) AS 'Amount Incl VAT', 
    CAST(IsNull(ar.Old_Amount_Incl_VAT,0) - IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2)) AS 'VAT Amount', 
    p.Currency_Code, 
    ( 
    SELECT TOP 1 pt.Payment_Type + ' ' + pt.Description AS Payment_Type FROM PaymentsPerPerson ppp 
    LEFT OUTER JOIN PaymentCodes pc ON ppp.Client_Id = pc.Client_Id AND ppp.Project_Id = pc.Project_Id AND ppp.Payment_Code = pc.Payment_Code 
    LEFT OUTER JOIN PaymentTypes pt ON ppp.Client_Id = pt.Client_Id AND ppp.Project_Id = pt.Project_Id AND pt.Payment_Type = pc.Payment_Type  
    WHERE ppp.Client_Id = ar.Client_Id AND  ppp.Project_Id = ar.Project_Id AND  ppp.Person_Id = ar.Person_Id AND  ppp.Line_Number IN (SELECT MAX(Line_Number) FROM PaymentsPerPerson WHERE Person_Id = ar.Person_Id) ) AS Payment_Type, 
    ( 
    SELECT TOP 1 convert(varchar, Payment_Date, 120) AS Payment_Date FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Payment_Date, 
    ( 
    SELECT TOP 1 Card_Reference FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Transaction_Id, 
    convert(varchar, getdate(), 120) AS Date 
FROM 
    AccountingReport ar 
LEFT OUTER JOIN Participants pa ON ar.Client_Id = pa.Client_Id AND ar.Project_Id = pa.Project_Id AND ar.Person_Id = pa.Person_Id AND pa.Date_Registered IS NOT NULL 
LEFT OUTER JOIN Projects p ON ar.Client_Id = p.Client_Id AND ar.Project_Id = p.Project_Id 
RIGHT OUTER JOIN PaymentsPerPerson ppp ON ar.Client_Id = ppp.Client_Id AND ar.Project_Id = ppp.Project_Id AND ar.Person_Id = ppp.Person_Id 
WHERE 
    ar.Client_Id = 'CLIENTID' AND 
    ar.Project_Id = 'PROJECTID' AND 
    (IsNull(Old_Amount_Excl_VAT,0) <> 0 
    OR IsNull(Old_Amount_Incl_VAT,0) <> 0) 
    AND pa.Date_Registered IS NOT NULL 
ORDER BY 
    ar.Person_Id, 
    Item_Id, 
    SubItem_Id, 
    SubSubItem_Id 

I've tried using ROLLUP but this is what I get

This is the query I've used using rollup

SELECT 
    CASE WHEN (GROUPING(ar.Person_Id) = 1) THEN 0
            ELSE ISNULL(ar.Person_Id, 'UNKNOWN')
    END AS 'Id',
    CASE WHEN (GROUPING(pa.Serial_Number) = 1) THEN 0
            ELSE ISNULL(pa.Serial_Number, 'UNKNOWN')
    END AS Serial_Number,
    CASE WHEN (GROUPING(ar.Family_Name + ', '+ar.First_Name) = 1) THEN 'ALL'
            ELSE ISNULL(ar.Family_Name + ', '+ar.First_Name, 'UNKNOWN')
    END AS 'Name',
    CASE WHEN ar.Line_Type = 'A' THEN 'Activity: '+ar.Item_Name ELSE 'Hotel: '+ar.Item_Name END AS 'Description',
    SUM(CAST(IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2))) AS 'Amount Excl VAT', 
    SUM(CAST(IsNull(ar.Old_Amount_Incl_VAT,0) AS DECIMAL(10,2))) AS 'Amount Incl VAT', 
    SUM(CAST(IsNull(ar.Old_Amount_Incl_VAT,0) - IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2))) AS 'VAT Amount', 
    CASE WHEN (GROUPING(p.Currency_Code) = 1) THEN 'ALL'
            ELSE ISNULL(p.Currency_Code, 'UNKNOWN')
    END AS Currency_Code,
    ( 
        SELECT TOP 1 pt.Payment_Type + ' ' + pt.Description AS Payment_Type FROM PaymentsPerPerson ppp 
        LEFT OUTER JOIN PaymentCodes pc ON ppp.Client_Id = pc.Client_Id AND ppp.Project_Id = pc.Project_Id AND ppp.Payment_Code = pc.Payment_Code 
        LEFT OUTER JOIN PaymentTypes pt ON ppp.Client_Id = pt.Client_Id AND ppp.Project_Id = pt.Project_Id AND pt.Payment_Type = pc.Payment_Type  
        WHERE ppp.Client_Id = ar.Client_Id AND  ppp.Project_Id = ar.Project_Id AND  ppp.Person_Id = ar.Person_Id AND  ppp.Line_Number IN (SELECT MAX(Line_Number) FROM PaymentsPerPerson WHERE Person_Id = ar.Person_Id) 
    ) AS Payment_Type, 
    ( 
        SELECT TOP 1 convert(varchar, Payment_Date, 120) AS Payment_Date FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Payment_Date, 
    ( 
        SELECT TOP 1 Card_Reference FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Transaction_Id, 
    CONVERT(varchar, GETDATE(), 120) AS Date
FROM 
    AccountingReport ar 
LEFT OUTER JOIN Participants pa ON ar.Client_Id = pa.Client_Id AND ar.Project_Id = pa.Project_Id AND ar.Person_Id = pa.Person_Id AND pa.Date_Registered IS NOT NULL 
LEFT OUTER JOIN Projects p ON ar.Client_Id = p.Client_Id AND ar.Project_Id = p.Project_Id 
RIGHT OUTER JOIN PaymentsPerPerson ppp ON ar.Client_Id = ppp.Client_Id AND ar.Project_Id = ppp.Project_Id AND ar.Person_Id = ppp.Person_Id 
WHERE 
    ar.Client_Id = 'CLIENTID' AND 
    ar.Project_Id = 'PROJECTID' AND 
    (IsNull(Old_Amount_Excl_VAT,0) <> 0 
    OR IsNull(Old_Amount_Incl_VAT,0) <> 0) 
    AND pa.Date_Registered IS NOT NULL 
GROUP BY 
    ar.Client_Id,
    ar.Project_Id,
    ar.Person_Id,
    pa.Serial_Number,
    ar.Line_Type, ar.Item_Name, ar.Item_Id, ar.SubItem_Id, ar.SubSubItem_Id,
    p.Currency_Code,
    ar.Family_Name + ', '+ar.First_Name WITH ROLLUP
ORDER BY 
    ar.Person_Id, 
    Item_Id, 
    SubItem_Id, 
    SubSubItem_Id 

Any ideas would be great, since I'm no SQL expert.

Is it possible to do with SQL Server? Or do I need some script (asp,php) to generate this?

It'd be better if it can be done with SQL queries, since we export the results as Excel Reports when the client clicks on a button.

Answer 1

WITH
DataSet AS (
--Your original query. For simplicity, I hard-coded the results.
    SELECT  '4142722'AS ID, 1 AS Serial_Number, 'Name1' AS Name, 'Activity: Description' AS DESCRIPTION, 10000.00 AS AmountExclVAT,10000.00 AS AmountInclVAT, 0 AS VATAmount, 'EUR' AS Currency_Code, NULL AS Payment_Type, NULL AS Payment_Date, NULL AS Trasaction_ID, NULL as Date
    UNION
    SELECT  '4142722'AS ID, 1 AS Serial_Number, 'Name1' AS Name, 'Activity: Description1' AS DESCRIPTION, 2000.00 AS AmountExclVAT,2000.00 AS AmountInclVAT, 0 AS VATAmount, 'EUR' AS Currency_Code, NULL AS Payment_Type, NULL AS Payment_Date, NULL AS Trasaction_ID, NULL as Date
    UNION
    SELECT  '4142722'AS ID, 1 AS Serial_Number, 'Name1' AS Name, 'Activity: Description' AS DESCRIPTION, -1000.00 AS AmountExclVAT,-1000.00 AS AmountInclVAT, 0 AS VATAmount, 'EUR' AS Currency_Code, NULL AS Payment_Type, NULL AS Payment_Date, NULL AS Trasaction_ID, NULL as Date
    UNION
    SELECT  '4142724'AS ID, 3 AS Serial_Number, 'Name2' AS Name, 'Activity: Description' AS DESCRIPTION, 5000.00 AS AmountExclVAT,5000.00 AS AmountInclVAT, 0 AS VATAmount, 'EUR' AS Currency_Code, NULL AS Payment_Type, NULL AS Payment_Date, NULL AS Trasaction_ID, NULL as Date
    UNION
    SELECT  '4142724'AS ID, 3 AS Serial_Number, 'Name2' AS Name, 'Activity: Description' AS DESCRIPTION, 2000.00 AS AmountExclVAT,2000.00 AS AmountInclVAT, 0 AS VATAmount, 'EUR' AS Currency_Code, NULL AS Payment_Type, NULL AS Payment_Date, NULL AS Trasaction_ID, NULL as Date
)
,
SubTotals AS (
    SELECT
         ID
        ,NULL AS SERIAL_NUMBER
        ,NULL AS NAME
        ,'Subtotal' DESCRIPTION
        ,SUM(AmountExclVAT) sum_AmountExclVAT
        ,SUM(AmountInclVAT) sum_AmountInclVAT
        ,SUM(VATAmount) sum_VATAmount
        ,NULL AS CurrencyCode
        ,NULL AS Payment_Type
        ,NULL AS Payment_Date
        ,NULL AS Trasaction_ID
        ,NULL as Date
    FROM
        DataSet
    GROUP BY
         ID
        ,SERIAL_NUMBER
        ,NAME
)   
SELECT * FROM DataSet
UNION
SELECT * FROM SubTotals
ORDER BY ID, DESCRIPTION

Answer 2

If you're exporting to Excel, I think your best bet would be to export as is from your first query and leverage subtotaling in Excel (under the Data menu). You could automate this via a macro in Excel, or in your application code (upon clicking "Export" button).

Answer 3

Frederico, I created a Common Table Expression by using the WITH statement (the SQL statement before that NEEDS to be terminated with a semicolon), and added a separate UNION ALL part that uses a GROUP BY and creates the subtotals. I could not fully test it, as I did not get the table definitions and some ORDER BY column names are missing in the query, but here it is:

;

WITH Amounts (Id, Serial_Number, Name, Description, [Amount Excl VAT], [Amount Incl VAT], [VAT Amount], Currency_Code, Payment_type, Payment_Date, Transaction_Id, [Date] )
  AS (
  SELECT 
    ar.Person_Id AS 'Id', 
    pa.Serial_Number, 
    ar.Family_Name + ', '+ar.First_Name AS 'Name', 
    CASE WHEN ar.Line_Type = 'A' THEN 'Activity: '+ar.Item_Name ELSE 'Hotel: '+ar.Item_Name END AS 'Description', 
    CAST(IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2)) AS 'Amount Excl VAT', 
    CAST(IsNull(ar.Old_Amount_Incl_VAT,0) AS DECIMAL(10,2)) AS 'Amount Incl VAT', 
    CAST(IsNull(ar.Old_Amount_Incl_VAT,0) - IsNull(ar.Old_Amount_Excl_VAT,0) AS DECIMAL(10,2)) AS 'VAT Amount', 
    p.Currency_Code, 
    ( 
    SELECT TOP 1 pt.Payment_Type + ' ' + pt.Description AS Payment_Type FROM PaymentsPerPerson ppp 
    LEFT OUTER JOIN PaymentCodes pc ON ppp.Client_Id = pc.Client_Id AND ppp.Project_Id = pc.Project_Id AND ppp.Payment_Code = pc.Payment_Code 
    LEFT OUTER JOIN PaymentTypes pt ON ppp.Client_Id = pt.Client_Id AND ppp.Project_Id = pt.Project_Id AND pt.Payment_Type = pc.Payment_Type  
    WHERE ppp.Client_Id = ar.Client_Id AND  ppp.Project_Id = ar.Project_Id AND  ppp.Person_Id = ar.Person_Id AND  ppp.Line_Number IN (SELECT MAX(Line_Number) FROM PaymentsPerPerson WHERE Person_Id = ar.Person_Id) ) AS Payment_Type, 
    ( 
    SELECT TOP 1 convert(varchar, Payment_Date, 120) AS Payment_Date FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Payment_Date, 
    ( 
    SELECT TOP 1 Card_Reference FROM PaymentsPerPerson  WHERE Client_Id = ar.Client_Id AND Project_Id = ar.Project_Id AND Person_Id = ar.Person_Id ORDER BY Line_Number DESC 
    ) AS Transaction_Id, 
    convert(varchar, getdate(), 120) AS Date 
FROM 
    AccountingReport ar 
LEFT OUTER JOIN Participants pa ON ar.Client_Id = pa.Client_Id AND ar.Project_Id = pa.Project_Id AND ar.Person_Id = pa.Person_Id AND pa.Date_Registered IS NOT NULL 
LEFT OUTER JOIN Projects p ON ar.Client_Id = p.Client_Id AND ar.Project_Id = p.Project_Id 
RIGHT OUTER JOIN PaymentsPerPerson ppp ON ar.Client_Id = ppp.Client_Id AND ar.Project_Id = ppp.Project_Id AND ar.Person_Id = ppp.Person_Id 
WHERE 
    ar.Client_Id = 'CLIENTID' AND 
    ar.Project_Id = 'PROJECTID' AND 
    (IsNull(Old_Amount_Excl_VAT,0) <> 0 
    OR IsNull(Old_Amount_Incl_VAT,0) <> 0) 
    AND pa.Date_Registered IS NOT NULL 
)
SELECT Id, 1 AS Level, Serial_Number, Name, Description,
    [Amount Excl VAT],
    [Amount Incl VAT],
    [VAT Amount],
    Currency_Code, Payment_type, Payment_Date, Transaction_Id, [Date]
FROM Amounts
UNION ALL
SELECT Id, 2 AS Level, Serial_Number, Name, Description,
    SUM( [Amount Excl VAT] ) AS [Amount Excl VAT],  
    SUM( [Amount Incl VAT] ) AS [Amount Incl VAT], 
    SUM( [VAT Amount] ) AS [VAT Amount],
    Currency_code, Payment_type, Payment_Date, Transaction_Id, [Date]
    FROM Amounts
    GROUP BY Id, Serial_Number, Name, Description, currency_code, Payment_type, Payment_Date, Transaction_Id, [Date]
ORDER BY 
    Id, 
    Level,
    Item_Id, 
    SubItem_Id, 
    SubSubItem_Id

Answer 4

If the person_id is against one given person then we needn't use the person_id column in the roll up and if we change rollup to cube we'll get the out put for ALL of the particular persons name as well. Please do give it a try.