MySQL query return unexpected values

Question

Need to generate courses list and count

all
unanswered
answered but unchecked

Questions.

My database structure is looking like that

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

Explanation for some of tables:

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

Executing

    SELECT
    c.ID,
    c. NAME,
    COUNT(lq.id) AS Questions,
    COUNT(
        CASE
        WHEN a.id IS NULL THEN
            lq.id
        END
    ) AS UnAnswered,
    COUNT(
        CASE
        WHEN cr.id IS NULL THEN
            lq.id
        END
    ) AS UnChecked
FROM
    courses c
LEFT JOIN `courses-lessons` cl ON cl.cid = c.id
LEFT JOIN `lesson_questions` lq ON lq.lid = cl.lid
LEFT JOIN answers a ON a.qid = lq.qid
LEFT JOIN answer_chk_results cr ON cr.aid = a.id
GROUP BY
    c.ID

Tested it first on SQL fiddle with sample data. (Real data is huge, so I can't place it on sqlfiddle) It returned some values. Thought works well. But while I test it with real data, see that returns wrong values. Forex, when I manually count, result for all questions count must be 25, but it returns 27. Maybe I'm doing something wrong.

Note MySQL server running on my local machine, so I can give you teamviewer id and password if you want to connect to my desktop remotely and test query with real data.

The SQLfiddle to which you've linked and the query that you've given in your question are not the same. Which gives the incorrect results on your live data? — eggyal
– eggyal, Commented Aug 30, 2012 at 12:06
Well, I guess there might be a 1-to-n relation. As we don't see cardinalities anywhere this is a hard guess... — PepperBob
– PepperBob, Commented Aug 30, 2012 at 12:25

Gordon Linoff · Accepted Answer · 2012-08-30 13:43:24Z

1

I suspect the problem is that different joins are resulting in a multiplication of rows. The best way to fix this is by using subqueries along each dimension. The following is a more practical way. Replace the COUNTs in the select with COUNT DISTINCT:

SELECT c.ID, c. NAME,
       COUNT(distinct lq.id) AS Questions,
       COUNT(distinct CASE WHEN a.id IS NULL THEN lq.id END) AS UnAnswered,
       COUNT(distinct CASE WHEN cr.id IS NULL THEN lq.id END) AS UnChecked

Compared to COUNT, COUNT DISTINCT is a resource hog (it has to remove duplicates). However, it will probably work fine for your purposes.

answered Aug 30, 2012 at 13:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Muhammad Raheel · Accepted Answer · 2012-08-30 12:50:04Z

0

Use this query

SELECT
    c.ID,
    c.NAME,
    COUNT(lq.id) AS Questions,
    COUNT(IFNULL(a.id),lq.id)AS UnAnswered,
    COUNT(IFNULL(cr.id),lq.id)AS UnChecked,
FROM    courses c
LEFT JOIN `courses-lessons` cl ON cl.cid = c.id
LEFT JOIN `lesson_questions` AS lq ON lq.lid = cl.lid
LEFT JOIN answers a ON a.qid = lq.qid
LEFT JOIN answer_chk_results cr ON cr.aid = a.id
GROUP BY c.ID

edited Aug 30, 2012 at 12:50

answered Aug 30, 2012 at 12:21

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

MySQL query return unexpected values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related