What about using GROUP BY :
select date, item_id, item, count(*) as num
from test_popularity
group by date, item_id, item
order by count(*) desc
limit 0, 1
It'll get you :
+---------------------+---------+-------+-----+
| date | item_id | item | num |
+---------------------+---------+-------+-----+
| 2009-08-02 00:00:00 | 1 | Apple | 3 |
+---------------------+---------+-------+-----+
1 row in set (0.01 sec)
And, without the limit clause :
mysql> select date, item_id, item, count(*) as num
-> from test_popularity
-> group by date, item_id, item
-> order by count(*) desc
-> ;
+---------------------+---------+-------+-----+
| date | item_id | item | num |
+---------------------+---------+-------+-----+
| 2009-08-02 00:00:00 | 1 | Apple | 3 |
| 2009-08-04 00:00:00 | 2 | Peach | 2 |
| 2009-08-04 00:00:00 | 3 | Pear | 1 |
| 2009-08-05 00:00:00 | 1 | Apple | 1 |
| 2009-08-02 00:00:00 | 3 | Pear | 1 |
| 2009-08-06 00:00:00 | 1 | Apple | 1 |
| 2009-08-02 00:00:00 | 0 | Pear | 1 |
| 2009-08-06 00:00:00 | 2 | Peach | 1 |
| 2009-08-03 00:00:00 | 3 | Pear | 1 |
| 2009-08-06 00:00:00 | 3 | Pear | 1 |
| 2009-08-03 00:00:00 | 2 | Peach | 1 |
| 2009-08-04 00:00:00 | 1 | Apple | 1 |
+---------------------+---------+-------+-----+
12 rows in set (0.00 sec)
Of course, you will only get the number of items that correspond to the group by clause...
To get the total number, you'll need :
- either to iterate through the result set in PHP, calculating the sum (OK if you don't have too many results)
- or do another query.
I don't think there is a way to get both informations (number per day, and total number) in once query...
Using another query would be like this :
mysql> select item_id, item, count(*) as num
-> from test_popularity
-> group by item_id, item
-> ;
+---------+-------+-----+
| item_id | item | num |
+---------+-------+-----+
| 0 | Pear | 1 |
| 1 | Apple | 6 |
| 2 | Peach | 4 |
| 3 | Pear | 4 |
+---------+-------+-----+
4 rows in set (0.00 sec)
And, now, you just have to "merge" the results from both queries, in PHP.
This doesn't seem to be do-able in plain SQL... Or maybe with sub-queries, but I guess that wouldn't be too good performance-wise...
EDIT : and in only one query using a sub-query to get the total number :
mysql> select date, item_id, item, count(*) as num, (
-> select count(*)
-> from test_popularity sub_table
-> where sub_table.item_id = test_popularity.item_id
-> ) as total_num
-> from test_popularity
-> group by date, item_id, item
-> order by count(*) desc;
+---------------------+---------+-------+-----+-----------+
| date | item_id | item | num | total_num |
+---------------------+---------+-------+-----+-----------+
| 2009-08-02 00:00:00 | 1 | Apple | 3 | 6 |
| 2009-08-04 00:00:00 | 2 | Peach | 2 | 4 |
| 2009-08-06 00:00:00 | 2 | Peach | 1 | 4 |
| 2009-08-04 00:00:00 | 1 | Apple | 1 | 6 |
| 2009-08-06 00:00:00 | 3 | Pear | 1 | 4 |
| 2009-08-02 00:00:00 | 3 | Pear | 1 | 4 |
| 2009-08-04 00:00:00 | 3 | Pear | 1 | 4 |
| 2009-08-02 00:00:00 | 0 | Pear | 1 | 1 |
| 2009-08-05 00:00:00 | 1 | Apple | 1 | 6 |
| 2009-08-03 00:00:00 | 3 | Pear | 1 | 4 |
| 2009-08-06 00:00:00 | 1 | Apple | 1 | 6 |
| 2009-08-03 00:00:00 | 2 | Peach | 1 | 4 |
+---------------------+---------+-------+-----+-----------+
12 rows in set (0.00 sec)
With this, you get :
- Data about each item
- number of times each item is in the table, per day
- and, for each item, total number of times it is in the whole table, independantly of the day