Shakkhar Shakkhar - 1 month ago 9
MySQL Question

Group data across both dimensions in MySql

I am something of a MySql (and Sql) noob, so excuse the vagueness of the question. Please ask if you need clarification. I will provide a somewhat contrived example to illustrate the problem.

Suppose I have a school database. The school is going to organize the annual sports day, and the information about who signed up for the event is stored in a table that looks like this:

+---------------+-------+------------+
| signup_status | grade | student_id |
+---------------+-------+------------+
| True | 1 | 1001 |
| True | 2 | 2010 |
| True | 1 | 1101 |
| True | 2 | 2002 |
| False | 1 | 1012 |
+---------------+-------+------------+


Now the schools wants a breakdown by grade of how many kids signed up for the event. So they want to see something like this:

+----------------+----------------------------+
| Grade / Status | Signed up | Didn't sign up |
+----------------+-----------+----------------+
| 1 | 2 | 1 |
| 2 | 2 | 0 |
+----------------+-----------+----------------+


I know I can run something like

SELECT count(`student_id`) as `count`, IF(`status`, 'Yes', 'No') as `signed_up`, `grade`
GROUP BY `grade`, `signed_up`
ORDER BY `grade` ASC


to get this:

+-----------+---------------+-----------+
| Count | Signed up | Grade |
+-----------+---------------+-----------+
| 2 | Yes | 1 |
| 1 | No | 1 |
| 2 | Yes | 2 |
| 0 | No | 2 |
+-----------+---------------+-----------+


My question is, how do I go from here to what I want, as described above?

Thanks in advance.

Answer Source

Use conditional aggregation:

select grade, sum(status) as signedup, sum(not status)
from t
group by grade;

MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false.