Combine multiple count statements into single select statement

Question

I have table to track the success, failure of students taking exam in a course as follows.

      Column   |  Type   |                        Modifiers                        
------------+---------+---------------------------------------------------------
 id         | integer | not null default nextval('assessment_id_seq'::regclass)
 student_id | integer | not null
 lesson_id  | integer | not null
 correct    | boolean | default false

Now, I need to generate a report of the students. Report just shows the number of attempts as total, and number of correct ones as score - per lesson.

select count(*) as score from assessment where correct = true and student_id = 1 group by lesson_id 

select count(*) as total_attempts from assessment  where student_id = 1 group by lesson_id .

I would like to combine these two queries to a single query. How can i do this..

Thanks.

The Scrum Meister · Accepted Answer · 2011-02-14 05:34:32Z

3

SELECT COUNT(*) as total_attempts,
  COUNT(CASE WHEN correct = true THEN 1 ELSE NULL END) as score
FROM assessment
WHERE student_id = 1
GROUP BY lesson_id

answered Feb 14, 2011 at 5:34

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combine multiple count statements into single select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related