Geek asks and answers

Get count from same column in mysql table?

I would like to count the same field for different values, for example:

user {user_id, gender}

Gender may have men or women :)

I want to get an account for all men and women, i.e.

COUNT(male)   COUNT(female)  
   4               16

but im confused because they come from the same gender coloumnthanks

+5
source share
4 answers

Try this for the line result:

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

Conclusion:

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

Try this for the result of a row with a grand total:

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

Conclusion:

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

Try this for a gradient 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;

Conclusion:

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

See this SQLFiddle

+11
source

Based on your comment, you want to calculate for both men, women, and for the total:

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

+2
source

-

SELECT
  COUNT(IF(gender = 'M', User_id, NULL) males,
  COUNT(IF(gender = 'F', User_id, NULL) females
FROM
  User
0

: :

Select gender,count(*) as count
From User
Group by gender
with rollup

Demo SQl Fiddle

0
source

More articles:


All Articles