MySQL multi join / group query

Question

idI have a table like this

|----|-----------|--------|
| ID | Ethnicity | Gender |
| 1  |    2      |   1    |
| 2  |    3      |   1    |
| 3  |    4      |   2    |
|----|-----------|--------|
etc ....

And I'm trying to get back a set of results that show me ethnicities group by male(1) and female(2)

So the result row would in this example would be:

Ethnicity    Male    Female
    2         1        0
    3         1        0
    4         0        1

So far I'm close with to what I want with:

SELECT ethnicity, 
(SELECT 
        count(id)
    FROM
        table_name
    WHERE
        gender = '2' ) as female,
(SELECT 
        count(id)
    FROM
        table_name
    WHERE
        gender = '1') as male
FROM table_name
GROUP BY ethnicity

Which gives me:

Ethnicity    Male    Female
2            2        1
3            2        1
4            2        1

But need the count(id) to only be a count of the adno of that ethnicity row if that makes sense.

Andriy M · Accepted Answer · 2013-01-22 09:07:04Z

2

Try this instead:

SELECT 
  ethnicity, 
  SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END) AS female,
  SUM(CASE WHEN gender = 2 THEN 1 ELSE 0 END) AS Male
FROM students
GROUP BY ethnicity;

The above should work in virtually any SQL product, not just in MySQL. If you like, however, you can also use this version, which employs MySQL's implicit conversion of booleans to ints (true -> 1, false -> 0):

SELECT 
  ethnicity, 
  SUM(gender = 1) AS female,
  SUM(gender = 2) AS Male
FROM students
GROUP BY ethnicity;

edited Jan 22, 2013 at 9:07

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Jan 21, 2013 at 15:25

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

5 Comments

timothystringer Over a year ago

Sorry the 1's and 0's are misleading, I'm looking for a count of the rows.

Ilion Over a year ago

Don't you mean SUM instead of MAX?

Mahmoud Gamal Over a year ago

@timothystringer then use SUM instead of MAX like in my edit.

Mahmoud Gamal Over a year ago

@AndriyM - Thanks for your edit, and thank for the tip. Didn't know that.

Strawberry Over a year ago

This (where the number of columns is known and finite) is an appropriate use of a PIVOT query. More generally, I think it's best to simply group the results (in this case by ethnicity AND gender) and handle the display logic at the application level.

Hamlet Hakobyan · Accepted Answer · 2013-01-21 15:29:48Z

0

SELECT 
  ethnicity, 
  CASE WHEN gender = 1 THEN 1 ELSE 0 END AS Male,
  CASE WHEN gender = 2 THEN 1 ELSE 0 END AS Female
FROM students

answered Jan 21, 2013 at 15:29

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

MySQL multi join / group query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related