SQL Count on Multiple Fields

Question

I'm not even sure if this is possible or whether I just need to write multiple queries.

I have a database table as follows:-

USER

university_id
gender

The output I'd like to achieve is as follows

| Uni  | Total Users | Male | Female
| Uni1 | 30          | 15   | 15
| Uni2 | 40          | 25   | 15

I would be grateful if you could let me know if this is possible to achieve in a single queries, or whether I should just use different queries for each result.

Thank you in advance.

Phill

Cristian Lupascu · Accepted Answer · 2014-09-26 09:24:26Z

3

Here's how you can do it in a single query:

select
  UniversityId,
  count(*) as `Total Users`,
  sum(case when gender = 'male' then 1 else 0 end) as Male,
  sum(case when gender = 'female' then 1 else 0 end) as Female
from User
group by UniversityId

Demo: http://www.sqlfiddle.com/#!2/e7d16a/4

answered Sep 26, 2014 at 9:24

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Count on Multiple Fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related