Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

getting count from the same column in a mysql table?

I wanted to a count of the same field for different values for example:

user{user_id, gender}

Gender can have obviously male or female :)

i want to get count for all the males and females i.e.

COUNT(male)   COUNT(female)  
   4               16

but im confused because they come from the same gender coloumn thanks

like image 353
user1551482 Avatar asked Aug 16 '12 11:08

user1551482


2 Answers

Try this for row wise result:

SELECT gender, COUNT(User_id) AS count
FROM User
GROUP BY gender;

Output:

| gender | count |
|--------|-------|
|      F |     4 |
|      M |     2 |

Try this for row wise result with grand total:

SELECT  (IFNull(gender,'Total')) AS gender,
COUNT(User_id) AS Count
FROM User
GROUP BY gender
WITH rollup;

Output:

| gender | Count |
|--------|-------|
|      F |     4 |
|      M |     2 |
|  Total |     6 |

Try this for column wise result:

SELECT
  COUNT(CASE WHEN gender = 'M' THEN User_id END) AS males,
  COUNT(CASE WHEN gender = 'F' THEN User_id END) AS females,
  COUNT(*) AS Total
FROM User;

Output:

| males | females | Total |
|-------|---------|-------|
|     2 |       4 |     6 |

See this SQL Fiddle

like image 153
Himanshu Jansari Avatar answered Sep 17 '22 08:09

Himanshu Jansari


Based on your comment, you want count for both males, females and the total count:

SELECT sum(case when gender = 'M' then 1 else 0 end) males,
  sum(case when gender = 'F' then 1 else 0 end) females,
  count(*) total
FROM  yourTable

See SQL Fiddle with Demo

like image 31
Taryn Avatar answered Sep 21 '22 08:09

Taryn