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.