MySQL query question...

[CavemanUK]

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

 

[jb27]

Assuming i understand correctly, try this:


SELECT COUNT(DISTINCT zip_code)
FROM <your table>


I think that will work.

 

[grapes911]

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.

SELECT <zip_code_column>, count(*)
FROM <table>
GROUP BY zip_code_column>;

 

[CavemanUK]

Assuming i understand correctly, try this:


SELECT COUNT(DISTINCT zip_code)
FROM <your table>


I think that will work.

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?

 

[grapes911]

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.

 

[CavemanUK]

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.

SELECT <zip_code_column>, count(*)
FROM <table>
GROUP BY zip_code_column>;

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?

 

[CavemanUK]

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.

thanks for your suggestions... worked perfectly... the final query looks like this...

SELECT SUBSTRING(postcode,1,4), count(*)
FROM Jobs
GROUP BY SUBSTRING(postcode,1,4)

 

[CavemanUK]

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?

SELECT SUBSTRING( postcode, 1, 4 ) , COUNT( * ) 
FROM Jobs
GROUP BY SUBSTRING( postcode, 1, 4 ) 
ORDER BY  `count` DESC

 

[grapes911]

I've never done it, but try Order By count(*)

 

[CavemanUK]

got it working by doing the following...

SELECT SUBSTRING( postcode, 1, 4 ) , COUNT( * ) as cnt
FROM Jobs
GROUP BY SUBSTRING( postcode, 1, 4 ) 
ORDER BY  `cnt` DESC

 

[grapes911]

That should work. The other didn't work because you were trying to order by 'count', but count(*) was not aliased as 'count'.