mysql - sql按2个字段计数分组？

Question

name status a win a run a run b run b win b win c run c run c run d run d run e NA e run e win f NA f NA f NA

在mysql中

在这里，我需要计算运行的次数，对于 win 和 NA 也不相同

谁能帮我吗？

预期输出 WIN 3 RUN 2 NA 1

尝试查询 SELECT a FROM data GROUP BY a HAVING ( COUNT(*) > 1 )

标签： mysqldatabase

Answer 1

尝试使用条件聚合：

SELECT
    name,
    COUNT(CASE WHEN status = 'win' THEN 1 END) AS WIN,
    COUNT(CASE WHEN status = 'run' THEN 1 END) AS run,
    COUNT(CASE WHEN status = 'NA'  THEN 1 END) AS NA
FROM yourTable
GROUP BY
    name;