CODEWITHSUNDEEP
mysqlsqldatabase
heron
heron

Reputation: 3661

MySQL COUNT unexpected results

My database structure looks like this:

https://docs.google.com/open?id=0B9ExyO6ktYcOenZ1WlBwdlY2R3c

Explanation for some of tables:

  1. answer_chk_results - checked answers table. So if some answer doesn't exist on this table, it means it's unchecked
  2. lesson_questions - lesson <-> question associations (by id) table

When I query database like this:

SELECT
    q.id,
    q.content,
    q.type
FROM
    `questions_and_exercises` q,
    `lesson_questions` lq
WHERE
    q.id = lq.qid
AND lq.lid = 1

I am getting all list of questions. But what I would like is a count of answers and checked answers for every question. When I use this query:

SELECT
    q.id,
    q.content,
    q.type,
    COUNT(DISTINCT a.ID) answer_count,
    COUNT(DISTINCT acr.id) checked_count
FROM
    `questions_and_exercises` q,
    `lesson_questions` lq
LEFT JOIN answers a ON a.qid = lq.qid,
LEFT JOIN `answer_chk_results` acr ON acr.aid = a.id
WHERE
    q.id = lq.qid
AND lq.lid = 1

The results is only 1 question with the wrong count of answers. (There are about 9-10 questions in my DB) What am I missing?

Upvotes: 1

Views: 303

Answers (1)

Andomar
Andomar

Reputation: 238096

A group by would help, like:

select  q.id
,       q.content
,       q.type
,       count(distinct a.id) as answer_count
,       count(acr.checked) as checked_count
from    questions_and_exercises q
join    lesson_questions lq
on      q.id = lq.id
left join
        answers a 
on      a.qid = lq.qid
left join
        answer_chk_results acr 
on      acr.aid = a.id
group by
        q.id
,       q.content
,       q.type

Note that count(acr.checked) returns the number of answers with a row in the answer_chk_results table. And count(distinct acr.checked) returns the number of different values of the checked column. If checked is a boolean, that would always be one or zero.

Upvotes: 5

Related Questions