postgresql case when使用实例

建表插入数据

CREATE TABLE test (
	ID serial,
	col INT,
	NAME VARCHAR (10)
);

INSERT INTO test (col, NAME)
VALUES
	(0, 'A'),
	(0, 'A'),
	(0, 'A'),
	(1, 'A'),
	(0, 'B'),
	(0, 'B'),
	(1, 'B'),
	(1, 'B');

SELECT
	*
FROM
	test;

结果：

SELECT
	NAME,
	SUM (CASE col WHEN 0 THEN 1 ELSE 0 END)  AS col1,
	SUM (CASE col WHEN 1 THEN 1 ELSE 0 END)  AS col2
FROM
	test
GROUP BY
	NAME;

结果：