1

I have this table below that I need to get the number of student that get A in their exam. This is what I want to achieve from table below:

3 A's = 0 student. 2 A's = 3 student.

+--------------+------------+------+
|  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    |
+--------------+------------+------+

I try with: 

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

The output is:

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

It not work. It will just count all the grade for specific student. Please help me solve this. Thank you.

sg552
  • 1,521
  • 6
  • 32
  • 59

2 Answers2

2
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 returns:

a_count   cnt
      2     3
Peter Lang
  • 54,264
  • 27
  • 148
  • 161
1

You can use a subquery:

SELECT num_as, COUNT(1) AS num_students
FROM (
  SELECT student_ID, COUNT(gred) AS num_as
  FROM data_exam
  WHERE gred = 'A'
  GROUP BY student_ID
) counts_by_student
GROUP BY num_as
Gary G
  • 5,692
  • 2
  • 27
  • 18
  • Thank you. It works but what does it mean by `COUNT(1)`? What does 1 in the count mean? – sg552 Oct 07 '12 at 15:26
  • `COUNT(1)` will count the number of rows in each group. `COUNT(expression)` counts the number of times the expression is not `NULL`, and the expression is evaluated for each row. So `COUNT(student_ID)` would give you the same thing, assuming that `student_ID` is never `NULL`. In this case we just want the number of rows, so any non-NULL expression will do. `COUNT(25)` will do exactly the same thing. – Gary G Oct 07 '12 at 17:07