Sub Query with group by returns more than 1 row Issue

by Wajid Hussain   Last Updated September 17, 2019 12:06 PM

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

Thanks in advance.

Answers 1

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) return 0; so if an attempted question is not correct (ELSE in theWHERE correct = 1), then we returnNULL` 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 typecast true to 1, and false to 0; thus giving us the desired count.

Madhur Bhaiya
Madhur Bhaiya
September 17, 2019 12:05 PM

Related Questions

Updated November 04, 2017 20:06 PM

Updated October 08, 2018 14:06 PM

Updated March 15, 2019 19:06 PM

Updated July 19, 2018 07:06 AM

Updated May 04, 2018 13:06 PM