MySQL query question...

Discussion in 'Web Design and Development' started by CavemanUK, Feb 13, 2011.

  1. CavemanUK macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #1
    Hi,

    I have a table that includes a postcode (or zipcode) as a varchar that has about 600 records. is it possible to query it and get a count of each different postcode using a query or do i have to use a script (like php) to loop through every record and search/compare

    thanks in advance..
     
  2. jb27 macrumors newbie

    Joined:
    Mar 4, 2010
    #2
    Assuming i understand correctly, try this:


    SELECT COUNT(DISTINCT zip_code)
    FROM <your table>


    I think that will work.
     
  3. grapes911 Moderator emeritus

    grapes911

    Joined:
    Jul 28, 2003
    Location:
    Citizens Bank Park
    #3
    I'm better at Oracle than MySQL, but I think it's done the same way. This would get all distinct zip codes and the number of times each zip code appears in the table.

    Code:
    SELECT <zip_code_column>, count(*)
    FROM <table>
    GROUP BY zip_code_column>;
    
     
  4. CavemanUK thread starter macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #4
    Hi jb27, thanks for the reply.. i think i should clarify and elaborate a little..

    say i have the following data in the postcode field of my jobs table.....

    LL185LN
    LL185UZ
    LL221UC
    LL323AC
    LL221AB
    LL221RE

    i would love the query to be able to return a count either based on exact matchs or preferably based on the first 4 chars. eg

    postcode count
    LL18 2
    LL22 3
    LL32 1


    is this possible?
     
  5. grapes911 Moderator emeritus

    grapes911

    Joined:
    Jul 28, 2003
    Location:
    Citizens Bank Park
    #5
    Use my code above but add a SUBSTR around the zip code column. Make sure you do it in both the SELECT and GROUP BY.
     
  6. CavemanUK thread starter macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #6
    Hi grapes911, that code worked great and did what you said it would. is there any way i can modify it to search based on the left 4 characters so i can count areas rather than postcode/zipcodes?
     
  7. CavemanUK thread starter macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #7
    thanks for your suggestions... worked perfectly... the final query looks like this...

    Code:
    SELECT SUBSTRING(postcode,1,4), count(*)
    FROM Jobs
    GROUP BY SUBSTRING(postcode,1,4)
     
  8. CavemanUK thread starter macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #8
    I tried adding a sort so i could have the most popular at the top of the list but i cant seem to get it to work... any idea what im doing wrong?

    Code:
    SELECT SUBSTRING( postcode, 1, 4 ) , COUNT( * ) 
    FROM Jobs
    GROUP BY SUBSTRING( postcode, 1, 4 ) 
    ORDER BY  `count` DESC 
     
  9. grapes911 Moderator emeritus

    grapes911

    Joined:
    Jul 28, 2003
    Location:
    Citizens Bank Park
    #9
    I've never done it, but try Order By count(*)
     
  10. CavemanUK thread starter macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #10
    got it working by doing the following...

    Code:
    SELECT SUBSTRING( postcode, 1, 4 ) , COUNT( * ) as cnt
    FROM Jobs
    GROUP BY SUBSTRING( postcode, 1, 4 ) 
    ORDER BY  `cnt` DESC
     
  11. grapes911 Moderator emeritus

    grapes911

    Joined:
    Jul 28, 2003
    Location:
    Citizens Bank Park
    #11
    That should work. The other didn't work because you were trying to order by 'count', but count(*) was not aliased as 'count'.
     

Share This Page