COUNT DISTINCT WITH CONDITION and GROUP BY

user_13903434 0
Viewed 3557

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)
if-statement count distinct group-by sql
3 Answers

You can try with below query

SELECT COUNT(DISTINCT Name), Status 
  FROM Table
  WHERE Status IS NOT NULL
 GROUP BY Status;
user_86527600

You can try using row_number()

select status,count(distinct name) as cnt from 
(
select name,date,status,row_number() over(partition by name order by date desc) as rn
from tablename
)A where rn=1 and status is not null
group by status
user_69148640 
SELECT status,COUNT(*) AS name_count 
FROM (SELECT DISTINCT status,name FROM TEMP WHERE status IS NOT NULL) 
GROUP BY status;

This should work but should the name_count of true be 2 since both Andrew and Mary have status as true? Atleast here is my answer after I ran my command

status | name_count
-------+-----------
false   | 1           
true  | 2

Let me know if you have any doubts no how the command works

user_133687470
Related