CODEWITHSUNDEEP
mysqlmysql-workbench
User051593
User051593

Reputation: 61

RETURN the amount of males and the amount of females in 1 table using MySQL

I need to RETURN the amount of males and the amount of females in 1 table using MySQL. I have created a query that return a tables with males and females rows, but my column is not being populated. its results =0;

Here is my query. I get the table but it doesn't get populated

SELECT COUNT(gender) AS 'Female', COUNT(gender) AS 'Male'
FROM customers
WHERE gender = 'female' AND 'male';

Any Suggestions,

Upvotes: 1

Views: 647

Answers (3)

PM 77-1
PM 77-1

Reputation: 13334

Since MySQL equates true with 1 and false with 0 you can use a shorter version:

SELECT
  SUM(gender = 'female') AS `Female`,
  SUM(gender = 'male')   AS `Male`
FROM customers

SQL FIDDLE

Upvotes: 0

Rigel1121
Rigel1121

Reputation: 2034

You can do the CASE statement too:

SELECT
    SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) AS 'Female',
    SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) AS 'Male'
FROM customers

Upvotes: 0

C3roe
C3roe

Reputation: 96363

This should work:

SELECT
  SUM(IF(gender = 'female', 1, 0)) AS 'Female',
  SUM(IF(gender = 'male', 1, 0)) AS 'Male'
FROM customers

The IF gets you a value of 1 or 0 depending on whether the gender is female (resp. male) or not, and then you just sum up those zeros and ones to get the overall count.

Upvotes: 4

Related Questions