Find the number of lost items in each group (ORACLE SQL)

Question

Say I have the following table with these records

Table: COMPARE_INDIV

+----------+--------------+--------------+
| ID_GROUP | INDIV_ID_OLD | INDIV_ID_NEW |
+----------+--------------+--------------+
|        1 |          123 | 567          |
|        1 |          123 | null         |
|        1 |          123 | 123          |
|        2 |          891 | null         |
|        2 |          891 | 456          |
+----------+--------------+--------------+

I want to insert entries into a table called COUNTS that counts the number of lost INDIV_ID_OLD for each GROUP_ID in the COMPARE_INDIV table. A INDIV_ID_OLD is lost if the INDIV_ID_OLD does not exists in the INDIV_ID_NEW column for each GROUP_ID. In this case, the COUNTS table would look like this.

Table: COUNTS

+----------+------------+--+
| ID_GROUP | LOST_COUNT |  |
+----------+------------+--+
|        1 |          0 |  |
|        2 |          1 |  |
+----------+------------+--+

How would I accomplish something like this?

Answer 1

If I understand correctly, you can use two levels of aggregation:

select id_group, sum(1 - present) as missing
from (select id_group, indiv_id_old,
             max(case when individ_id_new = indiv_id_old then 1 else 0 end) as present
      from COMPARE_INDIV ci
      group by id_group, indiv_id_old
     ) ci
group by id_group;