mysql - mysql统计字段值的方法

Question

我的 table -

pid     id  subject section
1        1  7            0
2        1  12           0
3        1  13           1
4        1  13           2
5        1  13           3
6        1  14           3
7        1  14           4
8        2  15           1
9        2  16           1

要获取的结果-

id subject total
1    7      1
1    12     1
1    13     3
1    14     2

我的查询-

SELECT DISTINCT(SUBJECT), count(section)
FROM mytable
GROUP BY section
WHERE id = 1

但是这是抛出一个错误，让我知道我做错了什么

错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 4: SELECT DISTINCT(SUBJECT), count(section) FROM mytable GROUP BY section WHERE id = 1 

Answer 1

语法错误是由于错位的 WHERE 子句造成的。它需要在 GROUP BY 之前。另外:

• 您正在按要求和的列进行分组。
• 您不必要地使用了 DISTINCT。

查询应该在这一行:

SELECT SUBJECT, count(section)
FROM mytable
WHERE id = 1
GROUP BY SUBJECT