I have a data set in MySQL where I'm trying to pull out the most popular majors for each Gender. I typed in an example table down below.
How can I go from the first table to the one below it so I can then pick the most popular major for each gender visually?
If y'all have ideas on a better way to get the most popular major, that would also be great. I have a gut instinct saying that 'partition by' would be useful but I'm not sure how to use that as I'm a new user.
| Major | Gender |
|---|---|
| Math | Male |
| Econ | Female |
| Sociology | Female |
| Nursing | Female |
| Math | Male |
| Econ | Female |
| Econ | Male |
| Nursing | Male |
| Math | Female |
| Econ | Male |
| Nursing | Female |
| Nursing | Female |
| Major | Male | Female |
|---|---|---|
| Math | 2 | 1 |
| Econ | 2 | 2 |
| Sociology | 0 | 1 |
| Nursing | 1 | 3 |
CodePudding user response：
That's simply:
select
Major,
sum(Gender='Male') Male,
sum(Gender='Female') Female
from mysterytablename
group by Major
CodePudding user response：
We can try to use the condition aggregate function.
SELECT Major,
COUNT(CASE WHEN Gender = 'Male' THEN 1 END) Male,
COUNT(CASE WHEN Gender = 'Female' THEN 1 END) Female
FROM T
WHERE Gender IN ('Male','Female')
GROUP BY Major