# complex query ???

• Jan 18, 2005, 04:53
Rami Yacoub
complex query ???
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??
• Jan 18, 2005, 07:22
Okay using a datefield to calculate age is in the mysql tutorial. You can do it like this:
Code:

`  (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:

Code:

```  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'```
• Jan 18, 2005, 07:29
Rami Yacoub
thank
thanks al lot
but how can i get count for all females in the same query??
:) :)
• Jan 18, 2005, 08:23
Add this line after the last case statement (don't forget to add a comma after that last case statement).

Code:

` sum(case when sex='F' then 1 else 0 end) as `totalwomen``
• Jan 18, 2005, 08:34
Rami Yacoub
thanks
Thanks Alot

:lips::lips::lips::angel2::angel2::angel2::cheer::cheer::angel::angel:
• Jan 18, 2005, 13:23