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
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 | 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 Try this query -
SELECT COUNT(IF(gender = 'M', User_id, NULL)) males, COUNT(IF(gender = 'F', User_id, NULL)) females FROM User try this: with total count:
Select gender,count(*) as count From User Group by gender with rollup 