Sql根据不同条件统计总数

tags: [sql,统计总数]

categories: 学无止境

copyright_author_href: https://www.xiaohuihui.net

copyright_url: https://blog.csdn.net/Android_xue/article/details/102802475
copyright_author: SunnyRivers

cover:

前言

经常会遇到根据不同的条件统计总数的问题，一般有两种写法：count 和 sum 都可以
数据准备：

方法一：Count

代码：

SELECT
	COUNT(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			NULL
		END
	) AS cnt0,
	COUNT(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			NULL
		END
	) AS cnt1
FROM
	USER;

结果：

方法二：sum

代码：

SELECT
	SUM(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			0
		END
	) AS cnt0,
	SUM(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			0
		END
	) AS cnt1
FROM
	USER;

结果：

当然也可以和 count 代码一样 ELSE 后面也写为 NULL

SELECT
	SUM(
		CASE
		WHEN age > 20
		AND age < 25 THEN
			1
		ELSE
			NULL
		END
	) AS cnt0,
	SUM(
		CASE
		WHEN age >= 25
		AND age < 30 THEN
			1
		ELSE
			NULL
		END
	) AS cnt1
FROM
	USER;