SQL. Как задать условие для агрегирующей функции COUNT?

Question

[Дмитрий Темников]

Есть некий блог, две условные сущности - "публикации" и "категории". Плюс таблица связки. Задача: вытащить список категорий, указав количество публикаций в категории в целом и количество публикаций в ней же за последнюю неделю.
Делаю так

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?

Answer 1

[Дмитрий Темников]

SELECT category.cat_id, category.name, category.description, COUNT(catinpub.pub_id) as "count", MAX(publication.date) as "lastdate",
COUNT(CASE WHEN publication.date BETWEEN '2021-03-27 00:00:00' AND '2021-04-01 23:59:59' THEN 1 ELSE NULL END) 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