SQL duplicate counting headache

user_10206994 0
Viewed 54

I have a problem that I’m not sure how to overcome and would really appreciate some direction.

Example data

Device  Project     status
a111    101     Ready
a222    102     Finished
a333    103     Ready
a444    104     Ready
a555    105     Ready
a111    106     Started
a111    107     Ready
a555    108     Started

I’m looking for a way to count the number of devices at ‘Ready’ status. However, when it comes to ‘Ready’ I only want to count it once AND don’t count it if that device has a status of anything other than ‘Ready’ in another project

Logic If ready in one or multiple projects = count once If ready in one but has a different status in any other project = do not count

Correct result should show only 2 as 'Ready'; 'a333' and 'a444'.

SELECT
'Ready' AS Header
, status
, count(distinct device)
FROM my_table
WHERE
project BETWEEN'101' AND '110' 
GROUP BY status
duplicates oracle sql
4 Answers

You may use such a SQL Select statement with HAVING clause :

select device  
  from tab 
 group by device
 having sum(case when status = 'Ready' then 1 else 0 end ) =  count(device);

 DEVICE
 ------
 a444
 a333
user_58413060

If you devices all of whose status are ready, then one approach uses group by and having:

select device
from my_table t
group by device
having min(status) = max(status) and min(status) = 'Ready';
user_11440350

You can use not exists :

select t.*
from table t
where not exists (select 1 from table t1 where t1.device = t.device and t1.status <> 'Ready');
user_55294450

I think table subtraction would be a good technique in this case. First select using distinct:

select distinct(device)
  from my_table
  where status= 'Ready'
Minus
select distinct(device)
  from my_table
  where status <> 'Ready'
;
user_103017740
Related