mysql - 计算2列Mysql

Question

计算 2 列

这是我的桌子

Table A
|----------------------|---------------------|------------------|
|          id          |        typec                NOM        |    
|----------------------|---------------------|------------------|
|           1          |        Plat         |        A         |
|----------------------|---------------------|------------------|
|           2          |    Steeple-chase    |        B         |
|----------------------|---------------------|------------------|
|           3          |        Haies        |        C         |
|----------------------|---------------------|------------------|

我希望输出为：

+------------+--------+--------------+---------+-----------+
|      nom   |  Plat  |Steeple-chase |  Haies  |   ALL     |
+------------+--------+--------------+---------+-----------+
|      A     |   10   |      2       |    8    |    20     |
+------------+--------+--------------+---------+-----------+
|      B     |  100   |      50      |   50    |   100     |
+------------+--------+--------------+------+--------------+
|      C     |   5    |       5      |    5    |   15      |
+------------+--------+--------------+---------+-----------+

目前，我运行此查询，但结果不好

SELECT nom, typec, count(*) AS count
FROM p_mu.cachedate
GROUP BY nom, typec;

谢谢你的帮助...

标签： mysql

Answer 1

我怀疑您正在寻找枢轴/条件聚合（如果您发布与所需结果匹配的示例数据，我会更确定）

SELECT nom, sum(case when typec ='plat' then 1 else 0 end) as 'plat', 
       sum(case when typec ='steeple_chase' then 1 else 0 end) as 'steeple-chase',
       sum(case when typec ='hais' then 1 else 0 end) as 'hais',
       count(*) AS 'all'
FROM p_mu.cachedate
GROUP BY nom;