SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast Rami Yacoub's Avatar
    Join Date
    Oct 2004
    Location
    jordan
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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??

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  3. #3
    SitePoint Enthusiast Rami Yacoub's Avatar
    Join Date
    Oct 2004
    Location
    jordan
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thank

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

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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`

  5. #5
    SitePoint Enthusiast Rami Yacoub's Avatar
    Join Date
    Oct 2004
    Location
    jordan
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thanks

    Thanks Alot



  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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".


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •