Question

Write a summary query WITH CUBE that returns LineItemSum (which is the sum of InvoiceLineItemAmount) grouped by Account (an alias for AccountDescription) and State (an alias for VendorState). Use the CASE and GROUPING function to substitute the literal value "*ALL*" for the summary rows with null values.

Answer 1

SELECT CASE WHEN GROUPING(AccountDescription) = 1

THEN '*ALL*' ELSE AccountDescription END AS Account,

CASE WHEN GROUPING(VendorState) = 1

THEN '*ALL*' ELSE VendorState END AS State,

SUM(InvoiceLineItemAmount) AS LineItemSum FROM AP.dbo.GLAccounts

JOIN AP.dbo.InvoiceLineItems ON GLAccounts.AccountNo = InvoiceLineItems.AccountNo

JOIN AP.dbo.Invoices ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID

JOIN AP.dbo.Vendors ON Invoices.VendorID = Vendors.VendorID

GROUP BY AccountDescription, VendorState WITH CUBE