Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to count items per category?

Tags:

sql

mysql

count

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.

like image 633
romvlads Avatar asked May 21 '12 15:05

romvlads


People also ask

How do you count items in a database?

In SQL, you can make a database query and use the COUNT function to get the number of rows for a particular group in the table. Here is the basic syntax: SELECT COUNT(column_name) FROM table_name; COUNT(column_name) will not include NULL values as part of the count.

What is group count?

Group Counting invites students to count out loud from 1 to 20 (or higher) as a group without a designated order. This strategy asks students to heighten their awareness of the group, practice patience and listening, and work together to accomplish a challenging group task.


2 Answers

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.

like image 182
Johno Avatar answered Sep 18 '22 13:09

Johno


How about this

SELECT field1, count(1) as Total
FROM myTable
GROUP BY field1
like image 23
Fahim Parkar Avatar answered Sep 18 '22 13:09

Fahim Parkar