PostgreSQL分组查询count字段作为smth和total count字段的一部分

Question

我有一个表:表1 (product_id,purchase_date，城市，市场，类别)

类别=("面包","肥皂","蔬菜","水果")

我需要结果在一个表(以下列):采购日期，城市，市场，蔬菜产品数量，所有产品数量

分组由前三个字段执行，以便能够使用前三个字段作为过滤器

Answer 1

不需要时禁用where子句。

-- PostgreSQL
SELECT purchase_date
, city
, market
, SUM(CASE WHEN category = 'vegetables' THEN 1 END) "count of vegetable products"
, COUNT(1) "count of all products"
FROM table1
WHERE market = ? AND city = ? AND purchase_date = ?
GROUP BY purchase_date;
, city
, market

Answer 2

需要条件聚合。在Postgres中，我推荐filter:

SELECT purchase_date, city, market
COUNT(*) FILTER (WHERE category = 'vegetables') as cnt_vegetables,
COUNT(*) as cnt
FROM table1
WHERE market = ? AND city = ? AND purchase_date = ?
GROUP BY purchase_date, city, market;