Query using counts with multiple conditions

Question

I'm using Postgres 9.1 to try and fetch some data.
I have three (fictitious/sanitized) tables:

Table "public.students"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 id           | uuid                        | not null
 name         | character varying           | 
 birth_date   | date                        | 
 last_exam_at | timestamp without time zone | 
 created_at   | timestamp without time zone | 
 code         | character varying           | 
 gender       | character varying           | 
Indexes:
    "students_id_key" UNIQUE CONSTRAINT, btree (id)
Referenced by:
    TABLE "exams" CONSTRAINT "exams_student_id_fkey"
                  FOREIGN KEY (student_id) REFERENCES students(id)

Table "public.exams_essays"
  Column  | Type | Modifiers 
----------+------+-----------
 exam_id  | uuid | 
 essay_id | uuid | 

Table "public.exams"
      Column       |            Type             | Modifiers 
-------------------+-----------------------------+-----------
 id                | uuid                        | not null
 student_id        | uuid                        | 
 created_at        | timestamp without time zone | 
 completed_at      | timestamp without time zone | 
 course            | character varying           | 

Table "public.essays"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 id              | uuid                        | not null
 essay_type      | character varying           | not null
 filename        | character varying           |

And I'm trying to get the following information, grouped by created_at::date and student_id:

total number of exams
number of history essays (exam.course = 'history')
number of english essays (exam.course = 'english')

Each of these queries are not too difficult to do individually, but putting them together is proving difficult.

Lamak · Answer 1 · 2014-02-18T18:14:07.610

1

SELECT COUNT(DISTINCT EX.exam_id) TotalExams,
       SUM(CASE WHEN E.course = 'history' THEN 1 ELSE 0 END) HistoryEssays,
       SUM(CASE WHEN E.course = 'english' THEN 1 ELSE 0 END) EnglishEssays
FROM public.exams AS EX
LEFT JOIN public.exams_essays AS EE
    ON EX.exam_id = EE.essay_id

edited Feb 18 '14 at 18:14

answered Feb 18 '14 at 18:03

Lamak

69,480
12
108
116

I need the essay count, though, which may be different from the exam count, since one exam may have many essays. – David N. Welton Feb 18 '14 at 18:11

score 1 · Accepted Answer · edited Feb 19 '14 at 09:53

1

SELECT created_at::date, student_id
      ,count(*) AS exams
      ,sum(CASE WHEN course = 'history' THEN essays ELSE 0 END) AS essays_hist
      ,sum(CASE WHEN course = 'english' THEN essays ELSE 0 END) AS essays_engl
FROM   public.exams x
LEFT   JOIN (
   SELECT exam_id AS id, count(*) AS essays
   FROM   public.exams_essays
   GROUP  BY exam_id
   ) s USING (id)
GROUP  BY created_at::date, student_id;

I aggregate the n-table before the join, thereby avoiding multiplication of rows to begin with. Makes the query a bit simpler (IMO) and faster.

edited Feb 19 '14 at 09:53

David N. Welton

1,875
18
33

answered Feb 19 '14 at 04:50

Erwin Brandstetter

605,456
145
1,078
1,228

@David: You are aware that `GROUP BY 1` is valid syntax, too, right? [Example.](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564) – Erwin Brandstetter Feb 19 '14 at 10:01
yes, I am (now) aware of that, but I had to look it up, and I found the query clearer, more amenable to fiddling with, and less brittle with everything spelled out. – David N. Welton Feb 19 '14 at 12:32

Query using counts with multiple conditions

2 Answers2