complex query ???

[Rami Yacoub]

hi
i wana make a query that get count of female , count of each range of female where less than 18, 18-25, 25-35, more than 35
and i have to make the range from birthday field....
is there any body can help me to make this query???
or told me if i can't and why??

[guelphdad]

Okay using a datefield to calculate age is in the mysql tutorial. You can do it like this:

  (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT (birth,5)) AS age from yourtable

Now if you want to separate out into those age categories you'd need to use CASE. You want to repeat the case for each age category:

  SELECT 
 SUM(CASE WHEN (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT(birth,5)) < 18 THEN 1 ELSE 0 END) as `under 18`,
  
 SUM(CASE WHEN (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT(birth,5)) between 18 and 25 THEN 1 ELSE 0 END) as `18 to 25`,
  
 SUM(CASE WHEN (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT(birth,5)) between 25 and 35 THEN 1 ELSE 0 END) as `25 to 35`,
  
  SUM(CASE WHEN (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5) < RIGHT(birth,5)) > 35 THEN 1 ELSE 0 END) as `over 35`
  
  FROM yourtablename
  
  WHERE sex='F'

[Rami Yacoub]

thanks al lot
but how can i get count for all females in the same query??

[guelphdad]

Add this line after the last case statement (don't forget to add a comma after that last case statement).

 sum(case when sex='F' then 1 else 0 end) as `totalwomen`

[Rami Yacoub]

Thanks Alot

[guelphdad]

No problem. You might want to change the title of this thread to something more descriptive. That way other people looking for something along these same lines can find it easier on this site. Something like "how do I group ages by birthdate".