How to count numbers which are defined in other table, also show zero counts

user_2492068 0
Viewed 41

This is the current situation:

Table1

key some_id date class
1 1 1.1.2000 2
1 2 1.1.2000 2
2 1 1.1.1999 3
... ... ... ...

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:

key date class cnt
1 1.1.2000 2 2
2 1.1.1999 3 1
... ... ... ...

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

parameter_key class_code
1 1
1 2
1 3
2 1
... ...

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:

key date class cnt
1 1.1.2000 1 0
1 1.1.2000 2 2
1 1.1.2000 3 0
2 1.1.1999 1 0
2 1.1.1999 2 0
2 1.1.1999 3 1
... ... ... ...

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?

oracle sql
1 Answers

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

user_15092640
Related