CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
David N. Welton
David N. Welton

Reputation: 1865

Query using counts with multiple conditions

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:

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

Upvotes: 0

Views: 51

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656431

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.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

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

Upvotes: 1

Related Questions