CODEWITHSUNDEEP
sqlpostgresql
Learner
Learner

Reputation: 57

SQL - filtering with "min" and groupby

I would like to come up with a query which does "min" calculation along with the filter(status='PENDING') so that it returns the correct min_id. Simply adding it in the where clause wouldn't solve my purpose. It might be easier to understand with the information below:

Table: 

  id    sub_id         status      il_flag

  21     889          FAILED       false
  22     889          PENDING      false
  23     889          PENDING      false

MyQuery:

select sub_id, 
       min(id) as min_id, 
       sum(case when status = 'PENDING' then 1 else 0 end) as pending_count,
       sum(case when status in ('ACCEPTED','FAILED') then 1 else 0 end) as invalid_count
    from sub_event where il_flag=false
    group by sub_id

Result from above query (returns Incorrect 'min_id'): 

sub_id  min_id  valid_count  invalid_count
 889      21       2            1

Expected result(minimum id of status='PENDING'): 

sub_id  min_id  valid_count  invalid_count
 889      22       2            1

In the above query, simply adding "status='PENDING'" in 'where' clause wouldn't solve my purpose because then we wont get the invalid_count.

I am using postgres 9.6

Thanks in advance !

Upvotes: 0

Views: 249

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Use conditional aggregation:

  min(case when status = 'PENDING' then id end) as min_pending_id,

Or, better yet, use filter:

min(id) filter (where status = 'PENDING')

The latter was introduced in Postgres 9.4, is not compatible with most other databases, and should have slightly better performance (it is consistent with the ANSI standard).

EDIT:

In fact, you can write the whole query as:

select sub_id, 
       min(id) filter (where status = 'PENDING') as min_id, 
       count(*) filter (where status = 'PENDING') as pending_count,
       count(*) filter (where status in ('ACCEPTED', 'FAILED')) as invalid_count
from subscription_event
where idl_flag = false
group by sub_id;

The more I use the filter clause, the more I like it. Never use case in an aggregation again!

Upvotes: 2

Related Questions