Databases
StackOverflow2013
Postsdbo
POmySQL query - show most popular item
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmySQL query - show most popular item
    primarykey
    data
    text
    <p>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?</p> <p>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).</p> <p>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.</p> <p>Edit: Sorry, I had the wrong total for '2009'08-04'. It should be 4.</p> <p>An example of what I need:</p> <pre> +------------+---------------------+-------+ | 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 | +------------+---------------------+-------+ </pre> <p>Here's an example table:</p> <pre> 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); </pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkenitech
    1. USkenitech
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmySQL query - show most popular item
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmySQL query - show most popular item
      1. USMez
      1. VTFavorite
    3. VO
      singulars
      1. POmySQL query - show most popular item
      1. USshantanuo
      1. VTFavorite
    1. COThis can't really be answered properly without knowing which date should be chosen for each item, or if this really even matters.
      singulars
      1. POmySQL query - show most popular item
      1. USDana the Sane
    2. COHuh? He wants the most popular for each date. That's how I interpret: "I need to find the most popular occurrence of an item grouped by date"
      singulars
      1. POmySQL query - show most popular item
      1. UShobodave
    3. COI need to group by the date field in which case each row would be a "summary" for that date.
      singulars
      1. POmySQL query - show most popular item
      1. USkenitech
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload