MySQL Grouping of data

Question

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?

Answer 1

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.

Answer 2

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