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

Question

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'?

Gordon Linoff · Accepted Answer · 2016-11-28 13:44:55Z

3

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.

answered Nov 28, 2016 at 13:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2016-11-28 13:44:19Z

0

You can use a conditional sum

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

or count, but then the ELSE part has to be NULL since COUNT counts all non-null values

SELECT count(case when is_male = 1 then 1 else null end) AS boys,
       count(case when is_male = 0 then 1 else null end) AS girls
FROM users

answered Nov 28, 2016 at 13:44

juergen d

205k40 gold badges305 silver badges377 bronze badges

sagi · Accepted Answer · 2016-11-28 13:44:44Z

0

That's conditional aggregation :

SELECT SUM(is_male) AS boys,
       SUM(is_male <> 1) AS girls
FROM users;

MySQL evaluates Boolean expressions as 1 and 0 , so no need for case expression or anything .

answered Nov 28, 2016 at 13:44

sagi

40.6k7 gold badges66 silver badges87 bronze badges