AlainIb AlainIb - 2 years ago 185
SQL Question

Count values in other table

I have two tables =

subjects
and
stats
.

-- subjects --
------------------
| id | name |
------------------
| 1 | subjecta |
| 2 | subjectb |
| 3 | subjectc |
| 4 | subjectd |
| 5 | subjecte |
| 6 | subjectf |
| 7 | subjectg |
| 8 | subjecth |
| 9 | subjecte |
| ... | subjectf |
------------------

-- stats --
-----------------------------------
| user_id | subject_id | correct |
-----------------------------------
| 1 | 1 | false |
| 1 | 1 | false |
| 1 | 2 | false |
| 4 | 3 | false |
| 4 | 4 | false |
| 4 | 5 | false |
| 2 | 1 | true |
| 2 | 1 | true |
| 2 | 2 | false |
| 2 | 2 | true |
| 2 | 3 | false |
---------------------------------


What I need, for example for a give
user_id
(like 2), to get ALL the subjects (from subjects table) and for the ones he do (from stats) the count of correct true/false like this:

--------------------------------------------------
| id | name | correct true | correct false|
----------------------------------|----------------
| 1 | subjecta | 2 | 0 |
| 2 | subjectb | 1 | 1 |
| 3 | subjectc | 0 | 1 |
| 4 | subjectd | 0 | 0 |
| 5 | subjecte | 0 | 0 |
| 6 | subjectf | 0 | 0 |
| 7 | subjectg | 0 | 0 |
| 8 | subjecth | 0 | 0 |
| 9 | subjecte | 0 | 0 |
| ... | subjectf | 0 | 0 |
----------------------------------|--------------|


I have no idea what to do this.

Answer Source

You can accomplish this with a join between your two tables, along with some conditional aggregation to tally the number of true and false answers.

SELECT
    t1.id,
    t1.name,
    SUM(CASE WHEN t2.correct = 'true'  THEN 1 ELSE 0 END) AS correct_true,
    SUM(CASE WHEN t2.correct = 'false' THEN 1 ELSE 0 END) AS correct_false
FROM subjects t1
LEFT JOIN stats t2
    ON t1.id = t2.subject_id AND
       t2.user_id = 2
GROUP BY t1.id, t1.name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download