Show number of results in each "filter item"

Question

Hi !

How can i get the number of each filter item in a seach?

Example... im doing a job/resumes portal... i seach a job title and i have some filters items like City, State , area and etc... How can i show the number of each filter in the select box example Contry: Brazil(22 results), USA(61 results)

Here a url with a sample: http://www.manager.com.br/resumes/resumes_result.php?&search_table=resumes&tipo_local=0&keyword=program&

Tkz Roberto

Answer 1

You should get acquainted with GROUP BY clause and count(*) aggregate function:

SELECT Country, count(*)
FROM items
GROUP BY Country;

And when you have absorbed the necessary minimum, take a look at useful extensions to the GROUP BY clause in MySQL, namely WITH ROLLUP.