paulducsantos paulducsantos - 6 months ago 15
MySQL Question

mysql query to count from related tables twice

I'm having a problem getting my query the way I want it. I have 2 tables:
Passages and Questions tables

The questions table has a foreign key of a Passage. The questions table has a column named status that is either active, inactive, or review. I was able to get the count of active questions but now I want to get the count of questions in review also but I cant seem to get it correct. The following is what I came up with so far but the questionsinreview column just gives me the same number as numquestions.

SELECT
p."id",
COUNT(q."PassageId") as numQuestions,
COUNT(q2."PassageId") AS questionsInReview,
ROUND(AVG(q."level")) as "questionLevel"
FROM
"Passages" as p
LEFT OUTER JOIN "Questions" as q ON p.id=q."PassageId" AND q."status" = 'active'
LEFT OUTER JOIN "Questions" as q2 on p.id=q2."PassageId" where q2."status" = 'review'
GROUP BY p.id
ORDER BY p.id ASC


How should I set up my query to be able to get the count of questions in review also?

EDIT:

After playing around with what was suggested here is what I got to work

SOLUTION:

SELECT
p.id,
SUM(CASE WHEN q."status" = 'active' THEN 1 ELSE 0 END) AS numQuestions,
SUM(CASE WHEN q."status" = 'review' THEN 1 ELSE 0 END) AS questionsInReview,
ROUND(AVG(CASE WHEN q."status" = 'active' THEN q."level" ELSE Null END)) as questionLevel
FROM "Passages" AS p
LEFT JOIN "Questions" as q ON p.id = q."PassageId"
GROUP BY p.id
ORDER BY p.id ASC

Answer

What about something like (untested):

SELECT p.id,  
       COUNT(q.id) AS numQuestions, 
       SUM(q.status = "review") AS questionsInReview, 
       ROUND(AVG(if(q.status = "active", q.level, null)) as questionLevel
FROM Passages AS p 
LEFT JOIN Questions as q ON p.id = q.PassageId AND q.status = 'active'
GROUP BY p.id 
ORDER BY p.id ASC