I'm trying to count how many of the objects appear in table 2 without the status of 3 or 5. So if it has the status of 3 or 5 I want to exclude it from the count. Where I'm stuck is there are duplicate values, as they have may more than one status. Further explanation below.
Table 1
Object_ID
1
2
3
4
5
Table 2
ID | object_id | status
1 2 2
2 2 3
3 2 5
4 3 2
5 3 2
6 3 7
END GOAL
Count how many object_ids have a status excluding 3 or 5. But also to ignore duplicates. In this example, the total count would be 1 (with the object_id being 3). As I need to find all of the rows in table 2, then essentially merge them together assuming neither of them has a status of 3 or 5.
Count. | object_id
1 | 3
SELECT Count(distinct(object_id))
FORM table_2
WHERE status <> 3 or status <> 5
I seem to be able to group them if the status is 3 or 5, but I can't seem to exclude them.
Hopefully, it makes sense, I've tried to simplify it so I don't have irrelevant code included.
One method without subqueries is:
select count(distinct object_id) - count(distinct case when status in (3, 5) then object_id end)
from table_2;
This counts the number of distinct object ids and then subtracts the number of distinct object ids that have the specified statuses.
More typically, I would approach this with two levels of aggregation:
select count(*)
from (select object_id
from table_2
group by object_id
having sum( status in (3, 5) ) = 0
) o
You can use aggregation:
select count(*)
from (
select object_id
from table2
group by object_id
having max(status in (3, 5)) = 0
) t