how to GROUP BY on specific column and COUNT() the other columns?

Question

I've queried a few tables and result is as below:

I want to count correct col upon ct_id(course) col. I queried a lot but gained no success. How to query such a thing?

for example in SELECT I should have two columns: -correctNo -incorrectNo for each course(ct_id).
Note: The last column(correct) is a derived column: IF(selected_answer=answer,1,0) as correct

Answer 1

I am not so sure what your exact requirement is, but i think you are looking for this..

CREATE TABLE test1(User_ID SMALLINT, Jqci_ID SMALLINT, Ct_ID SMALLINT, Correct BIT)

INSERT INTO test1
SELECT 29, 5, 8, 0 UNION ALL
SELECT 29, 25, 8, 1 UNION ALL
SELECT 29, 26, 8, 0 UNION ALL
SELECT 29, 27, 8, 0 UNION ALL
SELECT 29, 28, 8, 0 UNION ALL
SELECT 29, 34, 8, 1 UNION ALL
SELECT 29, 35, 8, 0 UNION ALL
SELECT 29, 36, 8, 0 UNION ALL
SELECT 29, 37, 8, 0 UNION ALL
SELECT 29, 38, 8, 0 UNION ALL
SELECT 29, 39, 8, 0 UNION ALL
SELECT 29, 41, 8, 1 UNION ALL
SELECT 29, 42, 8, 0 UNION ALL
SELECT 29, 47, 34, 0


SELECT
    Ct_ID,
    SUM(CASE WHEN Correct = 1 THEN 1 ELSE 0 END) AS Correct,
    SUM(CASE WHEN Correct = 0 THEN 1 ELSE 0 END) AS InCorrect
FROM test1
GROUP BY Ct_ID

Answer 2

Something like:

select ct_id, sum(correct) as num_correct, sum(1-correct) as num_incorrect
from your_view group by ct_id;

may be the kind of thing you are looking for.