Search code examples
postgresqlcasedistinct

COUNT DISTINCT CASE expression for PostgreSQL Issues

I am trying to count the unique values of a position_id while also satisfy another criteria of "true" in another column. I keep getting all values returned since I am counting them.

COUNT(DISTINCT CASE WHEN p.position_id IS NOT NULL THEN 1
    WHEN internal_only = 'true' THEN 1 ELSE 0 END) AS I_Roles

Any help would be much appreciated.

Solution

  • You can use filtered aggregation:

    count(distinct position_id) filter (where internal_only = 'true')