CODEWITHSUNDEEP
mysqlsqlcount
romvlads
romvlads

Reputation: 165

How to count items per category?

I want to make a filtering of products on a site. Something like this: 

Department
- lassics (13,395)
- Literary (111,399)
- History (68,606)
...

Format
- HTML (3,637)
- PDF (8)
- Audio CD (443)
...

Language
- English (227,175)
- German (10,843)
- French (10,488)
...

How to count products per category? A separate SQL-query for each category would be too slow because there are too many products and categories. I suggest caching is not an option too.

Maybe it makes sense to use MySQL EXPLAIN queries (though it not always provide adequate information)? Or maybe using sphinx search engine for counting?... What is the best way to do this? Thanks.

Upvotes: 8

Views: 27470

Answers (3)

Fahim Parkar
Fahim Parkar

Reputation: 31627

How about this

SELECT field1, count(1) as Total
FROM myTable
GROUP BY field1

Upvotes: 10

Johno
Johno

Reputation: 1959

Try:

SELECT category, COUNT(*) as count FROM table GROUP BY category

The response should be all the different category values, and the number of occurrences of each.

Upvotes: 14

John Conde
John Conde

Reputation: 219804

Use COUNT() with the GROUP BY to group them together by category

Upvotes: 5

Related Questions