Let’s say in your database you have a posts table with an id column and a status column among others:
In an admin panel, if we wanted to show the count of the posts with a particular status, we could write separate queries for each status like this.
select count(*) from posts where status = 'published'; select count(*) from posts where status = 'scheduled'; select count(*) from posts where status = 'drafted';
A better approach would be to perform a single query using Conditional Aggregates. In MySQL, you can use a
case statement inside the parenthesis of the
select count(case status when 'published' then 1 end) as published, count(case status when 'scheduled' then 1 end) as scheduled, count(case status when 'drafted' then 1 end) as drafted from posts;
The results should show the count of each status in a separate column.
as keyword at the end of the count function is aliasing it as the name of the status for convenience. If you don’t include it, the name of each column would be the entire
count() function call.