Oracle SQL: Join two queries into one result set using count() and group by()

Question

• I have two tables to select and join data from.
• Both share corresponding key values.
• The select statement of the second table shall count() and group by() values.

An example and the expected result shall explain the situation:

Table One

| id  | rev | colour |
| --- | --- | ------ |
| 1   | a   | blue   |
| 2   | a   | green  |
| 1   | b   | grey   |

Table Two

| id  | rev | note     |
| --- | --- | -------- |
| 1   | a   | rejected |
| 1   | a   | removed  |
| 1   | b   | rejected |
| 1   | b   | rejected |
| 2   | a   | removed  |
| 2   | a   | removed  |

Pseudo SQL of the expected result set

t1.id, t1.rev, t1.colour, t2.count(rejected), t2.count(removed)

Expected result

| id  | rev | colour | rejected | removed |
| --- | --- | ------ | -------- | ------- |
| 1   | a   | blue   | 1        | 1       |
| 2   | a   | green  | 0        | 2       |
| 1   | b   | grey   | 2        | 0       |

What is the best way to get this in one SQL Statement? I guess I should use two sub-selects and join those. Don't know how to put in the count(), group by() and join here.

Thanks for your ideas!

Answer 1

I think you just want aggregation and join. I would approach this as:

select *
from table1 t1 left join
     (select id, rev,
             sum(case when note = 'rejected' then 1 else 0 end) as rejected,
             sum(case when note = 'removed' then 1 else 0 end) as removed
      from table2 t2
      group by id, rev
     ) t2
     using (id, rev);

Doing the aggregation in the subquery and using the using clause makes it convenient to select all columns from table1 without having to list all of them.