areke areke - 4 months ago 8
SQL Question

How do I use group by with a default value?

Let's say I have the following table:

+-----------+------------+-------+
| quiz_type | student_id | score |
+-----------+------------+-------+
| class | NULL | 10 |
+-----------+------------+-------+
| class | NULL | 9 |
+-----------+------------+-------+
| student | A | 5 |
+-----------+------------+-------+
| student | B | 7 |
+-----------+------------+-------+
| student | A | 6 |
+-----------+------------+-------+


I want to get the sum of the scores for each student, but need to include the class scores for every student. In other words, I would like a result table like:

+------------+-------+
| student_id | score |
+------------+-------+
| A | 30 |
+------------+-------+
| B | 26 |
+------------+-------+


In reality, the quiz_type column doesn't exist. I need to do a
GROUP BY student_id
, but include the
NULL
values with every group. I've been struggling with this for quite a bit. Is there a good solution?

Answer

You can try this:

select 
    student_id,
    sum(score) + (select sum(score) from yourtable where student_id is null) as score
from yourtable
where student_id is not null
group by `student_id`

SQLFiddle Demo

Comments