Есть некий блог, две условные сущности - "публикации" и "категории". Плюс таблица связки. Задача: вытащить список категорий, указав количество публикаций в категории в целом и количество публикаций в ней же за последнюю неделю.
Делаю так
SELECT category.cat_id, category.name, category.description, COUNT(catinpub.pub_id) as "count",
MAX(publication.date) as "lastdate",
COUNT(publication.date BETWEEN '2021-03-27 00:00:00' AND '2021-04-01 23:59:59') as "qntlastweek"
FROM category
LEFT JOIN catinpub ON catinpub.cat_id = category.cat_id
LEFT JOIN publication ON publication.pub_id = catinpub.pub_id
WHERE category.archive is NULL
GROUP BY category.cat_id
ORDER BY count DESC
Все хорошо, но проблема в "ней же за последнюю неделю"
Поставить условие publication.date BETWEEN '2021-03-27 00:00:00' AND '2021-04-01 23:59:59' в блок WHERE некорректно, иначе ограничится список публикаций. В запросе можно поставить в целом абсолютно любое значение, например COUNT(1) или COUNT(0), но в поле qntlastweek все равно выводит значение первого count.
Как задать условие для агрегирующей функции COUNT?