CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Marcus Junius Brutus
Marcus Junius Brutus

Reputation: 27286

group by and SUM only rows satisfying a criterion, but show the others too

I have two tables:

  1. subject which holds a list of subjects and their credits
  2. exams which shows which subjects the students failed or succeeded in the exams

I am trying to get a list of total credits earned for every student. Problem is if a student failed in all subjects he doesn't show up cause he's filtered out in the WHERE clause before the GROUP BY.

Here's the SQL:

CREATE TABLE IF NOT EXISTS subject (
  name VARCHAR(50) NOT NULL,
  credits INT  NOT NULL
);
ALTER TABLE subject ADD PRIMARY KEY (name);

INSERT INTO subject(name, credits) VALUES('ALGEBRA', 100);
INSERT INTO subject(name, credits) VALUES('FRENCH' ,  10);


CREATE TABLE IF NOT EXISTS  exam (
student VARCHAR(50) NOT NULL,
subject VARCHAR(50) NOT NULL,
success BOOLEAN     NOT NULL);
ALTER TABLE exam ADD PRIMARY KEY (student, subject);
ALTER TABLE exam ADD CONSTRAINT exam_2_subject  FOREIGN KEY (subject) REFERENCES subject(name);

INSERT INTO exam(student, subject, success) VALUES('Bob', 'ALGEBRA', true);
INSERT INTO exam(student, subject, success) VALUES('Bob', 'FRENCH', false);

INSERT INTO exam(student, subject, success) VALUES('Mike', 'ALGEBRA', false);
INSERT INTO exam(student, subject, success) VALUES('Mike', 'FRENCH', false);

CREATE VIEW student_credits AS
SELECT a.student, SUM(b.credits) AS total_credits FROM
exam    a INNER JOIN
subject b ON b.name = a.subject
WHERE a.success IS TRUE
GROUP BY a.student;

View student_credits only shows:

student     total_credits
--------------------------
Bob         100

What's an idiomatic way to fix view student_credits to also include Mike who failed all subjects?

Upvotes: 0

Views: 60

Answers (3)

Petr Chudanic
Petr Chudanic

Reputation: 547

Try this

CREATE VIEW student_credits AS
    SELECT a.student, SUM(CASE WHEN a.success then b.credits
                         else 0 end) AS total_credits FROM
    exam    a INNER JOIN
    subject b ON b.name = a.subject
    GROUP BY a.student;

Upvotes: 2

peter.petrov
peter.petrov

Reputation: 39457

select 
    a.student, 
    sum( (case when a.success then 1 else 0 end) * (b.credits) ) 
        as total_credits 
from
exam a
inner join subject b on a.subject = b.name
group by a.student;

Unfortunately seems there's no simpler way to convert boolean to int in PostgreSQL.

Upvotes: 0

Marcus Junius Brutus
Marcus Junius Brutus

Reputation: 27286

I did:

CREATE VIEW student_credits AS
SELECT a.student, SUM(b.credits) AS total_credits FROM
exam    a INNER JOIN
subject b ON b.name = a.subject
WHERE a.success IS TRUE
GROUP BY a.student
UNION
SELECT DISTINCT STUDENT, 0 AS total_credits FROM exam a
WHERE NOT EXISTS (SELECT 1 FROM exam b WHERE b.student=a.student AND success IS TRUE);

But I think that's ugly. Plus this is just an SSCCE, the real case would be more complicated.

Upvotes: 0

Related Questions