Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've queried a few tables and result is as below:
enter image description here
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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.