Postgresql: how to get results count with min of a field and unique of b field?

Question

I have a posgresql table as below:

id       session_id       result
1          111               success
2          111               fail
3          111               fail
4          222               fail
5          222               fail
6          222               success
7          333               success

There are three sessions in this table, with session ids 111, 222, and 333; Each session has multiple records, but the session_id is the same; and the result of the record with the smallest id determines whether that session is successful or failed.

The id 1 and id 4 and id 7 records in the above sample table determine whether a session is successful or unsuccessful.

Now I want get the total of success sessoins and fail sessions, how to write the SQL? I've tried the below:

SELECT COUNT(DISTINCT(session_id)) min(id) FROM logs WHERE result = success;
SELECT COUNT(DISTINCT(session_id)) min(id) FROM logs WHERE result = fail;

I expected the number of successful sessions to be two and the number of failed sessions to be one, but I got the error.

How can I get the number of successful and unsuccessful sessions?

Thanks

Answer 1

You may use distinct on with custom order by and conditional aggregation with filter clause.

with t as 
(
 select distinct on (session_id) result
 from logs
 order by session_id, id -- pick the smallest id for the session
)
select count(*) filter (where result = 'success') as success_cnt,
       count(*) filter (where result = 'fail') as fail_cnt
from t;

See demo