Count 2 columns 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;

感谢您的帮助...

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;

Answer 2

您要查找的函数不是 COUNT((，而是 + 运算符

SELECT nom, typec1, typec2, typec3, typec1+typec2+typec3 AS ALL
FROM p_mu.cachedate
GROUP BY nom

试试这是否有效并分享结果！