CODEWITHSUNDEEP
phpsqlmysql
text
text

Reputation: 363

MySQL Grouping of data

I want to group my data by Age and by gender: like this sample data:

Age: 1
      Male: 2
      Female: 3
      Age 1 Total: 5

Age: 2
      Male: 6
      Female: 3
      Age 2 Total: 9

How can I group the data according to age and count all the male and females in that age from mysql database?

Upvotes: 2

Views: 598

Answers (2)

ceteras
ceteras

Reputation: 3378

SELECT
  age,
  SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) males,
  SUM(CASE WHEN gender ='female' THEN 1 ELSE 0 END) females,
  COUNT(*) total
FROM yourtable
GROUP BY age

Upvotes: 11

Peter Lang
Peter Lang

Reputation: 55524

Select age, gender, Count(*) cnt
From your_table
Group By age, gender

will get you

Age  Gender   cnt
  1  Male     2
  1  Female   3
  2  Male     6
  2  Female   9

You should be able to sum the counts per age in PHP afterwards.

Upvotes: 7

Related Questions