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.