This is the current situation:

Table1

I'm counting the classes and providing the information through a view by using following select statement:

SELECT key, date, class, count(class) as cnt 
FROM table1 
GROUP BY key, date, class

The result would be:

but now there is another table which includes all possible class-codes, e.g.

For my view I'm only querying data for parameter_key 1. And the view now needs to show all possible class_codes, also if the count would be 0.

So my desired result table is:

but I just can't get my head around how to do this. I've tried to add a right join like this but that does not change anything (probably because I join the class column and do an aggregate which won't be displayed if there is nothing to count?):

SELECT key, date, class, count(class) as cnt 
FROM table1 
RIGHT JOIN table2 on table1.class = table2.class and table2.parameter_key = 1 
GROUP BY key, date, class

Any idea on how to achieve the desired result table?

CodePudding user response：

Use a PARTITIONed join:

SELECT t2.parameter_key AS key,
       t1."DATE",
       t2.class_code AS class,
       count(t1.class) as cnt 
FROM   table2 t2
       LEFT OUTER JOIN table1 t1
       PARTITION BY (t1."DATE")
       ON (t1.class = t2.class_code AND t1.key = t2.parameter_key)
WHERE  t2.parameter_key = 1
GROUP BY
       t2.parameter_key,
       t1."DATE",
       t2.class_code

Which, for the sample data:

CREATE TABLE table1 (key, some_id, "DATE", class) AS
SELECT 1, 1, DATE '2000-01-01', 2 FROM DUAL UNION ALL
SELECT 1, 2, DATE '2000-01-01', 2 FROM DUAL UNION ALL
SELECT 2, 1, DATE '1999-01-01', 3 FROM DUAL;

CREATE TABLE table2 (parameter_key, class_code) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL;

Outputs:

KEY	DATE	CLASS	CNT
1	1999-01-01 00:00:00	1	0
1	1999-01-01 00:00:00	2	0
1	1999-01-01 00:00:00	3	0
1	2000-01-01 00:00:00	1	0
1	2000-01-01 00:00:00	2	2
1	2000-01-01 00:00:00	3	0

Or, depending on how you want to manage the join conditions:

SELECT t1.key,
       t1."DATE",
       t2.class_code AS class,
       count(t1.class) as cnt 
FROM   table2 t2
       LEFT OUTER JOIN table1 t1
       PARTITION BY (t1.key, t1."DATE")
       ON (t1.class = t2.class_code)
WHERE  t2.parameter_key = 1
GROUP BY
       t1.key,
       t1."DATE",
       t2.class_code

Which outputs:

KEY	DATE	CLASS	CNT
1	2000-01-01 00:00:00	1	0
1	2000-01-01 00:00:00	2	2
1	2000-01-01 00:00:00	3	0
2	1999-01-01 00:00:00	1	0
2	1999-01-01 00:00:00	2	0
2	1999-01-01 00:00:00	3	1

db<>fiddle here