MySQL & ldquo; for each & rdquo;

I'm trying to make a code question website, like CodeChef.

So far, I have two tables, problems and submissions.

problems consists of a single id field which is auto_incremented. (It really has more fields, but they are not relevant.)

submissions consists of an id field which is auto_incremented, a problem_id field which is foreign key-ed to problems.id, and a bool correct field.

Here are their (simplified) creation statements:

create table problems
(
    id bigint auto_increment primary key not null,
    /* more fields that are unimportant */
);
create table submissions
(
    id bigint auto_increment primary key not null,
    problem_id bigint not null,
    correct bool not null,
    /* more fields */

    foreign key (problem_id) references problems(id)
);

Now, what I'm trying to do is the following:

For each id in problems, return a row consisting of that ID, the number of correct submissions for that problem, and the number of total submissions for that problem.

Basically, I want to use the following query:

select
    problems.id,
    sum(submissions.correct),
    count(submissions.id)
from submissions inner join problems
on submissions.problem_id=problems.id and problems.id=[problem_id];

where [problem_id] goes over every id in problems.

It seems like I need to use a sub-query, but I tried it and it did not work at all.

How do I create a query to return these results?

---

Your query has some defaults.

• You do not specify the grouping criteria for the aggregates you use
• you join, this is a 'for each' per se. You don't need anything else than the join condition you already specified
• Your join should be a left join since maybe there's no correct submission for your problem, leading to 0 results at all in case of an inner join.

This is my proposition:

SELECT
    p.id,
    SUM(IF(s.correct, 1, 0)) AS correct_submissions,
    COUNT(s.id) AS total_submissions
FROM problems p
        LEFT JOIN submissions s ON p.id = s.problem_id
GROUP BY
    p.id