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
CodePudding user response:
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