I need to find the most popular occurrence of an item grouped by date and display the total of all items along with the name of this item. Is something like this possible in a single query?
note: If they are all of equal occurrence (see last 3 rows in Insert) than I can just show at random or the first or last occurrence (whichever is easiest).
If this can't be done in the sql then will have to run through the result and sort the data via PHP which seems like it'll be quite messy.
Edit: Sorry, I had the wrong total for '2009'08-04'. It should be 4.
An example of what I need:
+------------+---------------------+-------+ | date | item | total | +------------+---------------------+-------+ | 2009-08-02 | Apple | 5 | | 2009-08-03 | Pear | 2 | | 2009-08-04 | Peach | 4 | | 2009-08-05 | Apple | 1 | | 2009-08-06 | Apple | 3 | +------------+---------------------+-------+
Here's an example table:
CREATE TABLE IF NOT EXISTS `test_popularity` (
`date` datetime NOT NULL,
`item` varchar(256) NOT NULL,
`item_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `test_popularity` (`date`, `item`, `item_id`) VALUES
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Pear', 3),
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Apple', 1),
('2009-08-02 00:00:00', 'Pear', 0),
('2009-08-03 00:00:00', 'Pear', 3),
('2009-08-03 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Apple', 1),
('2009-08-04 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Peach', 2),
('2009-08-04 00:00:00', 'Pear', 3),
('2009-08-05 00:00:00', 'Apple', 1),
('2009-08-06 00:00:00', 'Apple', 1),
('2009-08-06 00:00:00', 'Peach', 2),
('2009-08-06 00:00:00', 'Pear', 3);
My initial suggestion was incorrect:
SELECT
date, item, SUM(cnt)
FROM (
SELECT
date, item, count(item_id) AS cnt
FROM test_popularity
GROUP BY date, item_id
ORDER BY cnt DESC
) t
GROUP BY date;
This erroneously assumes that the outside aggregation (by date) will select the first row of the inner derived table which was ordered by cnt. This behavior is, in fact, undefined and not guaranteed to be consistent.
Here is the proper solution:
SELECT
t1.date, t1.item,
(SELECT COUNT(*) FROM test_popularity WHERE date = t1.date) as total
# see note!
FROM test_popularity t1
JOIN (
SELECT date, item, item_id, COUNT(item_id) as count
FROM test_popularity
GROUP BY date, item_id
) AS t2
ON t1.date = t2.date AND t1.item_id = t2.item_id
GROUP BY t1.date;
Note:
I added the (SELECT COUNT(*)) AS total because the question asked for this in one query. However, this will not scale as it is a correlated subquery. This means that for every t1.date the SELECT COUNT(*) subquery will run. Please benchmark and see if it performs suitably for your needs. If not, then I suggest getting the daily totals in a separate query. You would merge these results in your application.
thanks to hohodave for his initial response:
SELECT date, item, cnt, ( SELECT COUNT( * ) FROM test_popularity WHERE date = t.date ) AS totalCnt FROM ( SELECT date, item, count( item_id ) AS cnt FROM test_popularity GROUP BY date, item_id ORDER BY cnt DESC )t GROUP BY date;
This is as close as I could get....
SELECT DISTINCT p.date, ItemTotalsByDate.Item, DateTotals.Total
FROM test_popularity p
INNER JOIN
(SELECT date, MAX(cnt) DayMax from
(SELECT date, item, COUNT(*) cnt
FROM dbo.test_popularity
GROUP BY date, item) tbl
GROUP BY date) MaxesByDate
ON p. date = MaxesByDate.date
INNER JOIN
(SELECT date, item, COUNT(*) Total FROM dbo.test_popularity
GROUP BY date, item) ItemTotalsByDate
ON MaxesByDate.date = ItemTotalsByDate.date AND MaxesByDate.DayMax = ItemTotalsByDate.Total
INNER JOIN
(SELECT date, COUNT(*) Total FROM dbo.test_popularity
GROUP BY date) DateTotals
ON p.date = DateTotals.date
The only thing this leaves for your PHP to do is only display the first result it finds for a given date. I couldn't figure out a good way to arbitrarily pick one item when it was a tie. Hope this helps.