I'm constructing a SQL query for a business report. I need to have both subtotals (grouped by file number) and grand totals on the report.
I'm entering unknown SQL territory, so this is a bit of a first attempt. The query I made is almost working. The only problem is that the entries are being grouped -- I need them separated in the report.
Here is my sample data:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3B Mar 28/10 1 3 3B Mar 28/10 5 10
When I run this query
SELECT
CASE
WHEN (GROUPING(FileNumber) = 1) THEN NULL
ELSE FileNumber
END AS FileNumber,
CASE
WHEN (GROUPING(Date) = 1) THEN NULL
ELSE Date
END AS Date,
SUM(Cost) AS Cost,
SUM(Charge) AS Charge
FROM SubtotalTesting
GROUP BY FileNumber, Date WITH ROLLUP
ORDER BY
(CASE WHEN FileNumber IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
FileNumber,
(CASE WHEN Date IS NULL THEN 1 ELSE 0 END), -- Put NULLs after data
Date
I get the following:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 6 13 <-- 3B NULL 6 13 NULL NULL 17 38
What I want is:
FileNumber Date Cost Charge 3 Dec 22/09 5 10 3 Jan 13/10 6 15 3 NULL 11 25 3B Mar 28/10 1 3 <-- 3B Mar 28/10 5 10 <-- 3B NULL 6 13 NULL NULL 17 38
I can clearly see why the entries are being grouped, but I have no idea how to separate them while still returning the subtotals and grand total.
I'm a bit green when it comes to doing advanced SQL queries like this, so if I'm taking the wrong approach to the problem by using WITH ROLLUP
, please suggest some preferred alternatives -- you don't have to write the whole query for me, I just need some direction. Thanks!