Skip to main content

How to get Multiple counts based on different criteria in MySQL

I wanted to retrieve multiple counts in same table on different criteria. The sum of 1's works same as count where our expression is true. :-)

SELECT
sum(approved = 1) as visible_prop_count,
sum(approved = 2) as private_prop_count,
sum(approved = 3) as pending_app_prop_count,
sum(featured_property=1) as featured_prop_count,
sum(approved = 0) as draft
FROM `jos_mypod_property` WHERE 1

visible_prop_count private_prop_count pending_app_prop_count featured_prop_count draft
4 0 0 4 1


Comments