CODEWITHSUNDEEP
phpmysql
sg552
sg552

Reputation: 1543

Count and sum from table

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.

Upvotes: 1

Views: 151

Answers (2)

user1549550
user1549550

Reputation:

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

Upvotes: 1

Peter Lang
Peter Lang

Reputation: 55524

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

Upvotes: 2

Related Questions