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.
Thanks in advance.
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
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.