count distinct with condition和group by

dvtswwa3 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(348)

我想在一定条件下计算列中不同项目的数量。例如，如果表格如下所示：

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

如果我想将不同名称的数量计算为“名称计数”，其中最后一个日期的状态不为空，并按状态对它们进行分组，我应该怎么做？
结果应该是：

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)

sql group-by count if-statement Distinct

来源：https://stackoverflow.com/questions/62827949/count-distinct-with-condition-and-group-by

3条答案

按热度按时间

6bc51xsx1#

你可以试着用 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

赞(0）回复(0）举报 2021-07-24

q7solyqu2#

你可以试试下面的查询

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

赞(0）回复(0）举报 2021-07-24

kzipqqlq3#

SELECT status,COUNT(*) AS name_count 
FROM (SELECT DISTINCT status,name FROM TEMP WHERE status IS NOT NULL) 
GROUP BY status;

这应该行得通，但是由于安德鲁和玛丽的身份都是真的，那么真的名字应该是2吗？至少这是我执行命令后的答案

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

如果你对命令有任何疑问，请告诉我

赞(0）回复(0）举报 2021-07-24

我来回答

count distinct with condition和group by

3条答案

相关问题

热门标签

最新问答