Geek asks and answers

How to count items for each category?

I want to do product filtering on the site. Something like that:

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 calculate the number of products for each category? A separate SQL query for each category will be too slow because there are too many products and categories. I suggest caching is not an option either.

Maybe it makes sense to use MySQL EXPLAIN queries (although it does not always provide adequate information)? Or maybe using the sphinx search engine to count? ... What is the best way to do this? Thank you

+5
source share
3 answers

Try:

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

The answer should be all different values categoryand the number of occurrences of each of them.

+9
source

How about this

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

COUNT() GROUP BY,

+5

More articles:


All Articles