SQL, group one column and count its attributes

Question

Say I have a relation grades about students' grades like this:

|   ID | semester | Year | course_id | grade |
|------+----------+------+-----------+-------+
| 1018 | Fall     | 2002 |       272 | A+    |
|  107 | Fall     | 2002 |       274 | B     |
|  111 | Fall     | 2002 |       123 | C     |
/* a lot of data here */
|------+----------+------+-----------+-------+------------|

I wanna group by course_id and count its grades like this:

| course_id | semester | year | A+ | A- | B+ | B- | C+ | D+ | D- | else | sum |
|         1 | Fall     | 2009 | 11 |  8 | 10 |  1 |  1 |  1 |  1 |    1 |  34 |
|         2 | Fall     | 2009 |  1 |  1 |  1 |  1 |  1 |  1 |  1 |    1 |   8 |

I already figured out one solution but seems not satisfying to me:

/* use sum function */
select course_id, semester, year,
sum(if(grade = 'A+', 1, 0)) as 'A+',
sum(if(grade = 'A-', 1, 0)) as 'A-',
/* multiple lines */
from grades
group by course_id, semester, year;

I wonder if there is a more built-in way to make it, because my above solution is kinda of tricky and not general.

Can anyone offer better idea?

p.s.: yes it's a school assignment, and I want to seek more solutions:) It will be appreciated if give me more hints.

The only alternative to this of which I can think would be to use dynamic SQL to build the pivot query you want. But that might be out of scope for what you are doing. — Tim Biegeleisen, May 25 '18 at 01:58
@TimBiegeleisen thanks I found this: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — DoubleX, May 25 '18 at 02:02
Consider handling issues of data display in application code — Strawberry, May 25 '18 at 06:56
That's the perfect solution already, considering that you have before-known grades you want to show as columns. As shown in Gordon's answer, you can simplify this to `sum(grade = 'A+') as "A+"`, because true = 1 and false = 0 in MySQL. The last two columns would be `sum(grade not in ('A+', 'A-', 'B+', ...)) as "else", count(*) as "sum"`. — Thorsten Kettner, May 25 '18 at 07:33

score 2 · Answer 1 · answered May 25 '18 at 02:36

There is no "simpler" way to do this. Well, actually, I would simplify the logic (assuming MySQL) to:

select course_id, semester, year,
       sum( grade = 'A+') as `A+`,
       sum(i grade = 'A-') as `A-`,
       /* multiple lines */
from grades
group by course_id, semester, year;

However, that is probably not what you are looking for. A SQL query returns a fixes set of columns, with their names and types fixed. If you want the columns to be based on the actual values, then you cannot readily use a simple SQL statement.

You can use dynamic SQL, but that is even more complicated than your SQL query.

SQL, group one column and count its attributes

1 Answers1