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