WEB Need help with MySQL query

Discussion in 'OT Technology' started by GotVtec, Jan 27, 2008.

  1. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    I have a table that contains a number of entries with ages 1 to 75, I am trying to create a query so I can select the ages in intervals and count them up for each range.

    So it would give me a result similar to this:

    0-9: 3
    10-19: 35
    20-29: 12
    30-39: 22
    40-49: 21
    50-59: 2
    60+: 19
     
  2. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    select count(1) from table where age >= 0 AND age <= 9 group by age

    :dunno: that might work. didnt test it
     
  3. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    Code:
    $sql = "SELECT agegroup, count(*) AS total
                        FROM (SELECT
                              CASE WHEN age BETWEEN 0 AND 9 THEN '0 to 9'
                              CASE WHEN age BETWEEN 10 and 19 THEN '10 to 19'
                              CASE WHEN age BETWEEN 20 and 29 THEN '20 to 29'
                              CASE WHEN age BETWEEN 30 and 39 THEN '30 to 39'
                              CASE WHEN age BETWEEN 40 and 49 THEN '40 to 49'
                              CASE WHEN age BETWEEN 50 and 59 THEN '50 to 59'
                              CASE WHEN age >= 60 THEN '60 +' AS agegroup
                              FROM entries) entries
                        GROUP BY agegroup";
    
    I was attempting that, however, it errored out.
     
  4. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    whats the error?

    ive never use case when in SQL so i dunno what the correct syntax is
     
  5. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CASE WHEN age BETWEEN 0 AND 9 THEN '0 to 9' CASE WHEN
     
  6. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    yea ok. u sure u got the right syntax? cause just from an English grammatical standpoint i dont see what "Then '0 to 9'" achieves

    edit: oh ok i see what you're doing. maybe you need commas between each CASE ?
     
  7. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    Edit:

    Code:
    SELECT agegroup, count(*) AS total 
    					FROM (SELECT
    						  CASE WHEN age BETWEEN 0 AND 9 THEN '0 to 9'
    						  WHEN age BETWEEN 10 and 19 THEN '10 to 19'
    						  WHEN age BETWEEN 20 and 29 THEN '20 to 29'
    						  WHEN age BETWEEN 30 and 39 THEN '30 to 39'
    						  WHEN age BETWEEN 40 and 49 THEN '40 to 49'
    						  WHEN age BETWEEN 50 and 59 THEN '50 to 59'
    						  WHEN age >= 60 THEN '60 +' END AS agegroup
    						  FROM entries) entries
    					GROUP BY agegroup
     
  8. babygodzilla

    babygodzilla I love rice

    Joined:
    Nov 5, 2001
    Messages:
    3,108
    Likes Received:
    0
    hm? did u fix it?
     
  9. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    Yes:

    Code:
    SELECT
    CASE WHEN age BETWEEN 0 AND 9 THEN '0 to 9'
    WHEN age BETWEEN 10 and 19 THEN '10 to 19'
    WHEN age BETWEEN 20 and 29 THEN '20 to 29'
    WHEN age BETWEEN 30 and 39 THEN '30 to 39'
    WHEN age BETWEEN 40 and 49 THEN '40 to 49'
    WHEN age BETWEEN 50 and 59 THEN '50 to 59'
    WHEN age >= 60 THEN '60 +' END AS agegroup, count(age) AS total
    FROM entries
    GROUP BY agegroup
     

Share This Page