How to count items per category?

Question

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.

check related post : http://stackoverflow.com/questions/1136041/sql-tree-like-query-most-parent-group — Sachin Bhansali, May 21 '12 at 15:08

score 14 · Answer 1 · answered May 21 '12 at 15:05

14

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.

answered May 21 '12 at 15:05

Johno

1,959
1
15
15

@fahim has the best answer, one should always avoid using ```(*)``` when it can be avoided. – Quiche Dec 12 '20 at 11:17

score 10 · Answer 2 · answered May 21 '12 at 15:08

10

How about this

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

answered May 21 '12 at 15:08

Fahim Parkar

30,974
45
160
276

1

This is the best practice. Alwasy avoid using ```(*)``` if you can do something else – Quiche Dec 12 '20 at 11:16

score 5 · Answer 3 · answered May 21 '12 at 15:04

5

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

answered May 21 '12 at 15:04

John Conde

217,595
99
455
496

I know SQL syntax. It is not the case. I'm worried about performance. There will be nearly 500 000 items and the queries will be rather heavy with many parameters so it will take a lot of time for counting – romvlads May 21 '12 at 15:39

How to count items per category?

3 Answers3

Linked