Запрос с LEFT JOIN не возвращает строки для подсчета 0

Я пытаюсь получить следующее, чтобы вернуть счет для каждой организации, используя левое соединение в PostgreSQL, но я не могу понять, почему он не работает:

  select o.name as organisation_name,
         coalesce(COUNT(exam_items.id)) as total_used
  from organisations o
  left join exam_items e on o.id = e.organisation_id
  where e.item_template_id = #{sanitize(item_template_id)}
  and e.used = true
  group by o.name
  order by o.name

Использование coalesce не работает. Я на свой остроумный конец! Любая помощь, безусловно, будет оценена!

Чтобы уточнить, что не работает, на данный момент запрос возвращает значения только для организаций с числом больше 0. Я бы хотел, чтобы он возвращал строку для каждой организации, независимо от количества.

Определения таблиц:

TABLE exam_items
  id serial NOT NULL
  exam_id integer
  item_version_id integer
  used boolean DEFAULT false
  question_identifier character varying(255)
  organisation_id integer
  created_at timestamp without time zone NOT NULL
  updated_at timestamp without time zone NOT NULL
  item_template_id integer
  stem_id integer
  CONSTRAINT exam_items_pkey PRIMARY KEY (id)

TABLE organisations
  id serial NOT NULL
  slug character varying(255)
  name character varying(255)
  code character varying(255)
  address text
  organisation_type integer
  created_at timestamp without time zone NOT NULL
  updated_at timestamp without time zone NOT NULL
  super boolean DEFAULT false
  CONSTRAINT organisations_pkey PRIMARY KEY (id)

Ответ 1

Это должно работать:

SELECT o.name AS organisation_name
     , COUNT(e.id) AS total_used
FROM   organisations   o
LEFT   JOIN exam_items e ON e.organisation_id = o.id 
                        AND e.item_template_id = #{sanitize(item_template_id)}
                        AND e.used = true
GROUP  BY o.name
ORDER  BY o.name;

У вас был LEFT [OUTER] JOIN, но более поздние WHERE условия заставили его вести себя как обычный [INNER] JOIN.
Переместите условие (условия) в предложение JOIN, чтобы оно работало должным образом. Таким образом, в первую очередь объединяются только строки, которые удовлетворяют всем этим условиям (или столбцы из правой таблицы заполняются NULL). Как и у вас, соединенные строки проверяются на наличие дополнительных условий практически после LEFT JOIN и удаляются, если они не проходят, точно так же, как с простой JOIN.
COUNT() никогда не возвращает NULL для начала. Это исключение среди агрегатных функций в этом отношении. Поэтому ~~COALESCE(COUNT(col))~~ никогда не имеет смысла, даже с дополнительными параметрами. Руководство:

Следует отметить, что , за исключением count, эти функции возвращают нулевое значение, когда строки не выбраны.

Жирный акцент мой.

Ответ 2

Чтобы было понятно,

важная строка - GROUP BY MAIN_TABLE, которая будет обрабатывать значение NULL из SOME_TABLE

SELECT COUNT(ST.ID)
FROM MAIN_TABLE MT
LEFT JOIN SOME_TABLE ST ON MT.ID = ST.MT_ID

GROUP BY MT.ID -- this line is a must