CODEWITHSUNDEEP
mysqlsql
Shakkhar
Shakkhar

Reputation: 199

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.

Upvotes: 0

Views: 21

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 1

Related Questions