Pivot in SQLite with missing records

Question

I'd like to use SQLite to output a table of records based on two tables: students and marks. Data in the output is the highest mark recorded for a given student for a given class. I looked at SO #1237068, however, my query must output zeros for students with a record in the students table but no record in the marks table. This is what I'd like to see for student "Alex":

studid      name        subjid_3    subjid_4    subjid_5
----------  ----------  ----------  ----------  ----------
A1          Raam        55          60          70
B1          Vivek       60          80          0
C1          Alex        0           0           0

My (broken) SQL to create the table and issue query is here; this does not print a record for Alex. How do I modify the select statement to write a record for Alex?

create table marks (studid, subjid, marks);
create table students (studid, name);

insert into marks values('A1', 3, 50);
insert into marks values('A1', 3, 55);
insert into marks values('A1', 4, 60);
insert into marks values('A1', 5, 70);
insert into marks values('B1', 3, 60);
insert into marks values('B1', 4, 80);

insert into students values('A1', 'Raam');
insert into students values('B1', 'Vivek');
insert into students values('C1', 'Alex');

select si.studid, si.name,
max(case when gd.subjid = 3 then gd.marks else 0 end) subjid_3,
max(case when gd.subjid = 4 then gd.marks else 0 end) subjid_4,
max(case when gd.subjid = 5 then gd.marks else 0 end) subjid_5
from students si
join marks gd
on gd.studid = si.studid
group by si.studid, si.name;

score 1 · Accepted Answer · answered Jul 25 '18 at 23:18

This is conditional aggregation with a left join:

select s.studid,
       max(case when m.subjid = 3 then mark else 0 end) as subjid_3,
       max(case when m.subjid = 4 then mark else 0 end) as subjid_4,
       max(case when m.subjid = 5 then mark else 0 end) as subjid_5
from students s left join
     marks m
     on s.studid = m.studid
group by s.studid;

Pivot in SQLite with missing records

1 Answers1