0

I've been trying to create a query that counts the number of different types of events associated with each entry in another table

city                   job             job_events
id | name | state      id | city_id    id | job_id | type | info

Event type can be one of start, complete, failed, paused

A job can be paused multiple times, and can have a soft failure before a hard failure

I want to create a query that outputs the following:

city.id | number of jobs | num started jobs | num complete jobs | num failed jobs

I've tried

SELECT city.id, count(job.id) AS num_jobs, COUNT(started) AS started,
 COUNT(complete) AS complete, COUNT(failed) AS failed
FROM city 
LEFT OUTER JOIN job ON city.id = job.city_id
LEFT OUTER JOIN job_events AS started ON type = 'started'
   AND started.job_id = job.id
LEFT OUTER JOIN job_events AS complete ON type = 'complete'
   AND complete.job_id = job.id
LEFT OUTER JOIN job_events AS failed ON type = 'failed'
   AND info = 'hard' AND failed.job_id = job.id
WHERE city.state = 'NSW'
GROUP BY city.id;

That gave me way more results than there were jobs, then I tried

SELECT city_id, count(job.id) AS num_jobs, COUNT(started) AS started,
 COUNT(complete) AS complete, COUNT(failed) AS failed
FROM 
  (SELECT distinct (job.id) AS id, city.id AS "city_id"
   FROM job 
   JOIN city ON city.id = job.city_id
   WHERE city.state = 'NSW') AS jobs
LEFT OUTER JOIN
 (SELECT distinct job_id FROM job_events WHERE type = 'started') AS start 
 ON start.job_id = job.id
LEFT OUTER JOIN 
 (SELECT distinct job_id FROM job_events WHERE type = 'COMPLETE') AS complete
 ON complete.job_id = job.id
LEFT OUTER JOIN
 (SELECT distinct job_id FROM job_events WHERE type = 'failed' AND info = 'hard') 
  AS failed
  ON failed.job_id = job.id
GROUP BY city_id;

but likewise the results don't seem to match up.

Obviously, I'd appreciate a pointer on what to do to fix this, but also if I can get some help understanding what I've got conceptually wrong here in trying to build this query.

  • Could you provide your table structures in the form of DDL (CREATE TABLE blah (...)) and some sample data in the form of DML (INSERT INTO blah VALUES (...)) and your desired result. Maybe a small job_events type table might be useful for enforcing data integrity? – Vérace Jun 18 at 12:31
  • "but likewise the results don't seem to match up." This is a bit vague. Can you describe how it differed from what you want? Or provide example input, and example desired output? – jjanes Jun 18 at 12:37
  • Are you looking for count(*) filter (where ...)? – a_horse_with_no_name Jun 18 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.