Sergei Sergei - 12 days ago 8
MySQL Question

How to display mysql count results from one table in different columns?

I have got one table wit users.
I want to collect girls and boys and display count numbers of them.
I do this:

SELECT COUNT(`is_male`) AS 'boys' FROM `users` WHERE `is_male` = 1 UNION (SELECT COUNT(`is_male`) AS 'girls' FROM `users` WHERE `is_male` = 0)


But it shows results in one column 'boys', how I can display one row with 2 columns 'boys' and 'girls'?

Answer

Just use conditional aggregation:

SELECT SUM(is_male = 1) AS boys,
       SUM(is_male = 0) as girls
FROM users ;

Notes:

  • MySQL treats boolean expressions as numbers in a numeric context, with "1" as true and "0" as false (which is why this works).
  • Only use single quotes for column and date constants, not for column names.
  • There is no need to escape your column or table names. Queries with lots of back-ticks are harder to read.