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.
โx
COUNT(male) COUNT(female)
4 16
โ
but im confused because they come from the same gender coloumn
thanks
Advertisement
Answer
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 |
โ