I have the below table1:

```
| yyyy_mm_dd | id | feature         | status        |
|------------|----|-----------------|---------------|
| 2019-05-13 | 2  | pricing         | implemented   |
| 2019-05-13 | 2  | pricing         | first_contact |
| 2019-05-13 | 5  | reviews         | implemented   |
| 2019-05-13 | 5  | pricing         | implemented   |
| 2019-05-13 | 6  | reviews         | first_contact |
| 2019-05-13 | 6  | reviews         | implemented   |
| 2019-05-13 | 6  | promotions_geo  | first_contact |
| 2019-05-13 | 6  | prop_management | first_contact |

```

There are two statuses, implemented and first\_contact. I want to introduce a third which will be no\_contact. This will be the total count of ids minus the sum of ids in implemented and first\_contact status.

I can get the total number of ids from a secondary table like so:

```
select
    count(id)
from
    table2

```

So I've tried to union the above so I can get the total count of IDs which can then be subtracted:

```
select
    yyyy_mm_dd,
    feature,
    count(s.id) as implemented_and_first_contact_total,
    null as total_ids
from
    table1 s
where
    s.yyyy_mm_dd = '2020-05-06'
group by
    1,2,4
union all
select
    null as yyyy_mm_dd,
    null as feature,
    null as implemented_and_first_contact_total,
    count(id) as total_ids
from
    table2

```

Now I'm unsure how I can subtract implemented\_and\_first\_contact\_total from total\_ids in order to get a value for no\_contact and have this as a value within status column. Maybe a union isn't correct to use here?

---

Edit: output. Say it turns out there are 300 total ids. The output would look like this:

```
| yyyy_mm_dd | feature         | status        | id_count |
|------------|-----------------|---------------|----------|
| 2019-05-13 | pricing         | implemented   | 2        |
| 2019-05-13 | pricing         | first_contact | 1        |
| 2019-05-13 | pricing         | no_contact    | 297      |
| 2019-05-13 | reviews         | implemented   | 2        |
| 2019-05-13 | reviews         | first_contact | 1        |
| 2019-05-13 | reviews         | no_contact    | 297      |
| 2019-05-13 | promotions_geo  | first_contact | 1        |
| 2019-05-13 | promotions_geo  | no_contact    | 299      |
| 2019-05-13 | prop_management | first_contact | 1        |
| 2019-05-13 | prop_management | no_contact    | 299      |

```