postgresql - 选择分组依据并计算两列

我有这张桌子

+----+----------------------------+---------------+----------+
| ID |         CREATED_AT         |   CATEGORY    |   TYPE   |
+----+----------------------------+---------------+----------+
|  1 | 2017-11-23 23:00:40.221958 | SEM COBERTURA | callback |
|  2 | 2017-11-23 22:58:36.970052 | VENDA         | ativo    |
|  3 | 2017-11-23 22:47:03.956185 | SEM COBERTURA | ativo    |
|  4 | 2017-11-23 22:42:24.309915 | VENDA         | ativo    |
|  5 | 2017-11-23 22:32:48.780418 | SEM COBERTURA | callback |
|  6 | 2017-11-23 22:12:21.631433 | VENDA         | callback |
|  7 | 2017-11-23 22:09:38.52699  | SEM COBERTURA | ativo    |
|  8 | 2017-11-23 22:08:09.836343 | LIGACAO MUDA  | callback |
|  9 | 2017-11-23 22:08:07.058063 | SEM COBERTURA | callback |
| 10 | 2017-11-23 22:07:02.067439 | LIGACAO MUDA  | other    |
+----+----------------------------+---------------+----------+

在上表中，我希望对列类型进行分组和求和，并对“VENDA”类别求和，例如：
这就是我想要的

+----------+------------+----------------+
|   TYPE   | COUNT_TYPE | COUNT_CATEGORY_VENDA |
+----------+------------+----------------+
| callback |          5 |              1 |
| ativo    |          4 |              2 |
| other    |          1 |              0 |
+----------+------------+----------------+

类型“回调”出现5次，有1个类别“VENDA”，“ativo”出现4次，有2个“VENDA”。。。
要获取使用此查询的类型和计数类型：

SELECT TYPE, count(TYPE) AS COUNT_TYPE FROM table WHERE created_at BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 23:59:00' GROUP BY TYPE ORDER BY COUNT_TYPE DESC

有人能帮我吗？

最佳答案

在postgresql中可以使用case

SELECT TYPE, count(TYPE) AS COUNT_TYPE, SUM(case CATEGORY when 'VENDA' then 1 else 0 end) FROM table WHERE created_at BETWEEN '2017-11-23 00:00:00' AND '2017-11-23 23:59:00' GROUP BY TYPE ORDER BY COUNT_TYPE DESC