user3264863 user3264863 - 1 year ago 48
MySQL Question

how to write mysql query to get search result's counter with each category

I am working in one project (USING PHP & MYSQL) where i need to show searched results counter with each category. For example i searched "abc" word but it may happen that this word is founded in two category "category1" and "category2". so i want to know that how to write query which can show results as under

Total Search : 50 records

Category1 (20 records)
Category2 (30 records)

I have attached screen shot taken from digikey website. this is just to show you that how we can do with mysql query.

enter image description here

Answer Source

Taking an example of authors from different countries and grouping them by country to get the number of authors from each country(which is category in your question), query would look like(searching for "a" in name):

SELECT country,COUNT(*)  
FROM Table1
WHERE aut_name LIKE "%a%"
GROUP BY country;