sql - 将多个计数语句合并为单个 select 语句

Question

我有一个表格来跟踪学生参加类(class)考试的成功和失败情况，如下所示。

      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

现在，我需要生成一份学生报告。报告仅显示每节课的总尝试次数，以及正确次数的分数。

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 .

我想将这两个查询合并为一个查询。我该怎么做...

谢谢。

Answer 1

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