php - 从表中计数和求和

Question

我有下面这张表，我需要得到在考试中获得 A 的学生人数。这是我想从下表中实现的:

3 个 A = 0 个学生。 2 个 A = 3 个学生。

+--------------+------------+------+
|  student_ID  | kod_subjek | gred |
+--------------+------------+------+
| 746123096687 | 02         | A    |
| 746123096687 | 12         | B    |
| 746123096687 | 21         | A    |
| 860206145454 | 12         | A    |
| 860206145454 | 02         | A    |
| 881012085535 | 02         | A    |
| 881012085535 | 21         | A    |
+--------------+------------+------+

我尝试:

mysql> SELECT student_ID, COUNT(gred) FROM data_exam GROUP BY student_ID;

输出是:

+--------------+-------------+
| student_ID   | COUNT(gred) |
+--------------+-------------+
| 746123096687 |           3 |
| 860206145454 |           2 |
| 881012085535 |           2 |
+--------------+-------------+

没用。它只会计算特定学生的所有成绩。请帮我解决这个问题。谢谢。

Answer 1

SELECT a_count, COUNT(*) AS cnt
FROM
(
  SELECT COUNT(*) AS a_count
  FROM data_exam
  WHERE gred = 'A'
  GROUP BY student_id
) x
GROUP BY a_count
ORDER BY a_count

Example on SQL-Fiddle返回:

a_count   cnt
      2     3