CODEWITHSUNDEEP
sqlpostgresql
ddd
ddd

Reputation: 5029

How to count single field twice in one query based on different conditions

I want to count the same field twice, distinct based on different conditions from another subquery. The pseudo code would be something like this:

select countd(svc_ord_nbr) <if start_dt = end_dt> as fixed, 
       countd(svc_ord_nbr) <else> as unfixed
       from [subquery]

The subquery is basically getting the min and max of the activity date based on the same svc_ord_nbr

select svc_ord_nbr, date_part('day', min(e.START_DTM)) as start_dt,
    date_part('day', max(e.START_DTM)) as end_dt
    from order o join activity a on o.svc_activity_id = a.activity_id
    group by svc_ord_nbr

The answer in How to count same field twice based on a boolean suggests to do case when on the boolean. However, it does not take the distinct value into account. Is there a way to count the same field twice only for distanct values.

Upvotes: 0

Views: 61

Answers (2)

Michel Milezzi
Michel Milezzi

Reputation: 11105

You can use a filter on a aggregate function:

select count(distinct svc_ord_nbr) FILTER (WHERE start_dt = end_dt) as fixed, 
       count(distinct svc_ord_nbr) FILTER (WHERE start_dt <> end_dt) as unfixed
from [subquery];

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use conditional aggregation:

select 
  count(DISTINCT CASE WHEN start_dt = end_dt THEN svc_ord_nbr END) as fixed, 
  count(DISTINCT CASE WHEN start_dt <> end_dt THEN svc_ord_nbr END )as unfixed
from [subquery];

Alternatively using windowed functions:

select DISTINCT
  count(DISTINCT svc_ord_nbr) FILTER(WHERE start_dt = end_dt) OVER() as fixed, 
  count(DISTINCT svc_ord_nbr) FILTER(WHERE start_dt <> end_dt) OVER() as unfixed
from [subquery];

You could add PARTITION BY inside OVER if needed.

Upvotes: 1

Related Questions