How to generate "empty" aggregate results in SQL

aggregate-functions, group-by, oracle, sql

Solution

The auxiliary table doesn't have to be a real table, it can be a common table expression - at least if you can get all possible values (or all you're interested in) from the table itself. Using @Bob Jarvis' query to generate all possible combinations you can do something like:

WITH CTE AS (
    SELECT * FROM (SELECT DISTINCT a FROM T)
    JOIN (SELECT DISTINCT b, c FROM T) ON (1 = 1)
)
SELECT CTE.A, CTE.B, CTE.C,
    SUM(CASE WHEN T.A IS NULL THEN 0 ELSE 1 END), NVL(SUM(T.D),0)
FROM CTE
LEFT JOIN T ON T.A = CTE.A AND T.B = CTE.B AND T.C = CTE.C
GROUP BY CTE.A, CTE.B, CTE.C
ORDER BY CTE.A, CTE.B, CTE.C;

If you have fixed values that may not be in the table then it's a little more complicated (or uglier anyway, and gets worse with more possible values):

WITH CTE AS (
    SELECT * FROM (SELECT DISTINCT a FROM T)
    JOIN (SELECT 'B1' AS B FROM DUAL
        UNION ALL SELECT 'B2' FROM DUAL) ON (1 = 1)
    JOIN (SELECT 'C1' AS C FROM DUAL
        UNION ALL SELECT 'C2' FROM DUAL) ON (1 = 1)
)
SELECT CTE.A, CTE.B, CTE.C,
    SUM(CASE WHEN T.A IS NULL THEN 0 ELSE 1 END), NVL(SUM(T.D),0)
FROM CTE
LEFT JOIN T ON T.A = CTE.A AND T.B = CTE.B AND T.C = CTE.C
GROUP BY CTE.A, CTE.B, CTE.C
ORDER BY CTE.A, CTE.B, CTE.C;

But you have to join to something that knows about the 'missing' values. If the same logic is needed elsewhere, and you have fixed values, then a permanent table might be cleaner - maintenance may be needed either way of course. You could also consider a pipelined function to act as a surrogate table, but depends on volumes maybe.

Problem

I'm trying to refine a SQL query to make my reports looks better. My query reads data from one table, groups by a few colums and calculates some aggregate fields (counts and sums). ``` SELECT A, B, C, COUNT(*), SUM(D) FROM T GROUP BY A, B, C ORDER BY A, B, C ``` Now, let's assume B and C columns are some defined constant strings, for example, B can be `'B1'` or `'B2'`, C can be `'C1'` or `'C2'`. So, an example resultset is: ``` A | B | C | COUNT(*) | SUM(D) -------------------------------- A1 | B1 | C1 | 34 | 1752 A1 | B1 | C2 | 4 | 183 A1 | B2 | C1 | 199 | 8926 A1 | B2 | C2 | 56 | 2511 A2 | B1 | C2 | 6 | 89 A2 | B2 | C2 | 12 | 231 A3 | B1 | C1 | 89 | 552 ... ``` As you can see, for `'A1'` I have all four possible (B, C) combination, but that's not true for `'A2'`. My question is: how can I generate also summary rows for (B, C) combination not present, in fact, in the given table? That is, how can I print, for example, also these rows: ``` A | B | C | COUNT(*) | SUM(D) -------------------------------- A2 | B1 | C1 | 0 | 0 A2 | B2 | C1 | 0 | 0 ``` The only solution I can see is to create some auxiliarity tables with all (B, C) values and then make a RIGHT OUTER JOIN with that aux table. But I'm searching for a cleaner way... Thank you all.

Original source