How to get counts by several conditions in MySQL

I will record what I learned when I have to write several counts in several conditions with SQL.

There were two ways to do that.

 

01. Use Function Count and if clause.

It's simple we will combine function 'COUNT' with 'if' clause. You can omit 'GROUP BY' clause depend on your needs.

COUNT(expression) FROM table_name GROUP BY column_name;

if(condition, value for true, value for false)

Here is a example to query 3 size of User's height.

SELECT
	COUNT(if(height < 140, 1, null)) as small,
	COUNT(if(height < 160 AND height >= 140, 1, null)) as medium,
	COUNT(if(height >= 160, 1 ,null)) as tall
FROM
	User

 

02. Use case clauses.

Otherwise, you can use case clause instead of if clause.

CASE WHEN condition THEN value for true END

Of course here's a example for case clause also.

SELECT
	COUNT(case when height < 140 then 1 end) as small,
	COUNT(case when height < 160 AND height >= 140 then 1 end) as medium,
	COUNT(case when height >= 160 then 1 end) as tall,
FROM
	User