jeudi 9 juillet 2020

COUNT DISTINCT WITH CONDITION and GROUP BY

I want to count the number of distinct items in a column subject to certain conditions. For example if the table is like this:

ID | name   |    date    | status
---+--------+------------+--------
1  | Andrew | 2020-04-12 | true
2  | John   | 2020-03-22 | null
3  | Mary   | 2020-04-13 | null
4  | John   | 2020-05-27 | false
5  | Mary   | 2020-02-08 | true
6  | Andrew | 2020-02-08 | null

If I want to count the number of distinct names as "name count" where the last date's status is not null and group them by status, what should I do?

The result should be:

status | name_count
-------+-----------
true   | 1            ---> Only counts Andrew (ID 1 has the last date)
false  | 1            ---> Only counts John (ID 4 has the last date)  

Aucun commentaire:

Enregistrer un commentaire