mysql query to count from related tables twice

Question

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

Julien Palard · Accepted Answer · 2016-05-26 20:50:21Z

1

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

answered May 26, 2016 at 20:50

Julien Palard

12.1k3 gold badges42 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paulducsantos Over a year ago

This worked for me the best as it was the simplest solution. Edited the question to have the solution. Had to tweak this query a little to make it work

Nir Levy · Accepted Answer · 2016-05-26 20:56:19Z

1

The problem with your answer is that you are just counting rows- since you are joining, the number of rows is the same.

Try this approach:

SELECT id, sum(active) as sum_active, sum(review) as sum_review from (
  SELECT p.id, 
    CASE q.status WHEN 'active' 1 ELSE 0 AS active,
    CASE q.status WHEN 'review' 1 ELSE 0 AS review
  FROM Passages as p inner join Questions as q ON p.id = q.passageId
) group by id

The idea- the inner query will just give you binary columns for active and review. this way you can simply sum them up to get the result

answered May 26, 2016 at 20:56

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

Collectives™ on Stack Overflow

mysql query to count from related tables twice

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related