CODEWITHSUNDEEP
sqloracle-database
Hakaru
Hakaru

Reputation: 13

Count only if condition on another column met

I have to count IDs only if other columns condition met. IDs are not unique, as may incluse several steps.

Table looks like:

rownum | ID | key   | result

1      |100 | step1 | accepted
2      |100 | step2 | accepted
3      |100 | step3 | transfer
4      |101 | step0 | accepted
5      |101 | step1 | accepted
6      |101 | step2 | rejected
7      |102 | step0 | accepted
8      |102 | step1 | accepted
9      |103 | step1 | rejected
10     |104 | step1 | rejected
11     |104 | step1 | rejected
12     |104 | step1 | rejected

In the example I have 5 IDs (but in real table thousands of them), and I have to COUNT only IDs where condition met. Condition is pretty simple: key <> 'step0', thus my COUNT script should return value of 3.

If I try

COUNT ID
FROM myTable
WHERE key <> 'step0'

it returns wrong value, as WHERE clause applies prior COUNT

Any ideas appreciated.

Upvotes: 1

Views: 693

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Here is a method that doesn't require nesting aggregation functions and doesn't require a subquery:

select (count(distinct id) -
        count(distinct case when key = 'step0' then id end)
       )
from mytable;

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use distinct

select COUNT (distinct ID)
FROM myTable
WHERE ID not in ( select id from myTable where key = 'step0' and id is not null)

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

Use grouping with having clause

select sum(count(distinct ID)) as "Count"
  from myTable
 group by ID
 having sum(case when key = 'step0' then 1 else 0 end)=0;
-- or "having sum( decode(key,'step0',1,0) ) = 0" is also possible specific to Oracle

Count
-----
  3

e.g. use reverse logic, come from key = 'step0'

Demo

Upvotes: 0

Boneist
Boneist

Reputation: 23588

This should work:

SELECT COUNT(COUNT(DISTINCT id)) num_ids
FROM   your_table
GROUP BY id
HAVING MIN(CASE WHEN key = 'step0' THEN key END) IS NULL;

The outer COUNT applies to the whole query including the aggregation of the COUNT(DISTINCT id)- remove it, and you'd see three rows with a value of 1.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

Try using correlated subquery with not exists

select count(distinct ID)
from tablename a 
   where not exists (select 1 from tablename b where a.id=b.id and key = 'step0')

Upvotes: 1

Related Questions