Mysql – Sub Query with group by returns more than 1 row Issue

countgroup byMySQLquerysubquery

I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result.

This is my query

SELECT Count(*)                                       AS totalCat,
       attempted_questions.category_id_fk,
       (SELECT Count(*)
        FROM   attempted_questions
        WHERE  attempted_questions.correct = 1
        GROUP  BY attempted_questions.category_id_fk) AS correct
FROM   attempted_questions
GROUP  BY attempted_questions.category_id_fk

I want to get total questions count and total correct questions count from this table against the category, like this below result.

   Category   Total_Questions   Total_Correct
      1             4                 3
      2             3                 1

This above mentioned is the required result from my query but it is not working for me, I have tried to fix it but could not find any solution.

Please have a look at my attached image to see the database records and table structure.

enter image description here

Best Answer

Instead of using the subquery to get "count of correct questions", you can get the same count using conditional aggregation utilizing CASE .. WHEN statement with COUNT(..) in the main query itself:

SELECT category_id_fk AS category, 
       Count(*) AS total_questions,
       Count(CASE WHEN correct = 1 THEN 1 ELSE NULL END) AS total_correct
FROM   attempted_questions
GROUP  BY category

COUNT(NULL) returns 0; so if an attempted question is not correct (ELSE condition against WHERE correct = 1), we return NULL to avoid counting it as correct.

Another possibility can be using SUM(..) with CASE .. WHEN. We can also utilize MySQL's implicit typecasting of boolean to int:

SELECT category_id_fk AS category, 
       Count(*) AS total_questions,
       SUM(correct = 1) AS total_correct
FROM   attempted_questions
GROUP  BY category

correct = 1 returns either True or False for correct and incorrect questions, respectively. During SUM(..) operation on these boolean values, MySQL implicitly typecasts true to 1, and false to 0; thus giving us the desired count.

You can also write it in SQL standard way (by avoiding implicit typecasting), as follows:

SELECT category_id_fk AS category, 
       Count(*) AS total_questions,
       SUM(CASE WHEN correct = 1 THEN 1 ELSE 0 END) AS total_correct
FROM   attempted_questions
GROUP  BY category

The only difference with COUNT(..) approach here is that it we are returning 0 instead of NULL in the ELSE part for SUM(..).

Related Question