CODEWITHSUNDEEP
sqlmariadbgreatest-n-per-group
Jarrett Duskey
Jarrett Duskey

Reputation: 63

MySQL Get Latest Entry For All Stores

I am trying to get the latest rows from a table that records the cumulative sales total for each 15 minutes.

The basic layout of the table is

StoreID, Time, Sales, Tax, Date

I'd like to be able to return the latest records for each store id. The query I've tried so far is :

SELECT t.StoreID, MAX(t.Time) as 'Last Reported', t.Sales+t.Tax as Sales, t.Date FROM ( SELECT * FROM trickledata WHERE Date = '20180724' ) t GROUP BY t.StoreID;

This works, however it doesn't return the corresponding sales for the time. E.g.

+---------+---------------+-------+------------+ | StoreID | Last Reported | Sales | Date | +---------+---------------+-------+------------+ | 100 | 11:45:00 | 0.00 | 2018-07-24 | | 111 | 12:00:00 | 0.00 | 2018-07-24 | | 115 | 12:00:00 | 0.00 | 2018-07-24 | | 121 | 12:00:00 | 0.00 | 2018-07-24 | | 122 | 12:00:00 | 0.00 | 2018-07-24 | | 123 | 12:00:00 | 0.00 | 2018-07-24 | | 124 | 12:00:00 | 0.00 | 2018-07-24 | | 125 | 12:00:00 | 0.00 | 2018-07-24 | | 126 | 12:00:00 | 0.00 | 2018-07-24 | | 127 | 12:00:00 | 0.00 | 2018-07-24 | | 128 | 12:00:00 | 0.00 | 2018-07-24 | | 129 | 12:00:00 | 0.00 | 2018-07-24 | | 130 | 12:00:00 | 0.00 | 2018-07-24 | | 131 | 12:00:00 | 0.00 | 2018-07-24 | | 135 | 12:00:00 | 0.00 | 2018-07-24 | | 137 | 12:00:00 | 0.00 | 2018-07-24 | | 138 | 12:00:00 | 0.00 | 2018-07-24 | | 141 | 12:00:00 | 0.00 | 2018-07-24 | | 150 | 12:00:00 | 0.00 | 2018-07-24 | | 160 | 12:00:00 | 0.00 | 2018-07-24 | | 164 | 12:00:00 | 0.00 | 2018-07-24 |

But there have been sales at that time, because when I query that specific date and time for store 121 I get:

+---------+------------+----------+-------+------+ | StoreID | Date | Time | Sales | Tax | +---------+------------+----------+-------+------+ | 121 | 2018-07-24 | 12:00:00 | 65.79 | 4.55 | +---------+------------+----------+-------+------+

CREATE TABLE Statement:

``CREATE TABLE `trickledata` (
    `StoreID` INT(11) NULL DEFAULT NULL,
    `Date` DATE NULL DEFAULT NULL,
    `Time` TIME NULL DEFAULT NULL,
    `Sales` DECIMAL(10,2) NULL DEFAULT NULL,
    `Tax` DECIMAL(10,2) NULL DEFAULT NULL,
    UNIQUE INDEX `Date` (`Date`, `Time`, `StoreID`),
    INDEX `by_date` (`Date`),
    INDEX `by_store` (`StoreID`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
``

Sample Data: INSERT LOW_PRIORITY IGNORE INTO `trickle`.`trickledata_copy` (`StoreID`, `Date`, `Time`, `Sales`, `Tax`) VALUES ('0100', '20180724', '120000', '212.63', '15.37'), ('0100', '20180724', '114500', '212.63', '15.37'), ('0100', '20180724', '113000', '212.63', '15.37'), ('0100', '20180724', '111500', '212.63', '15.37'), ('0100', '20180724', '110000', '212.63', '15.37'), ('0100', '20180724', '104500', '212.63', '15.37'), ('0100', '20180724', '103000', '212.63', '15.37'), ('0100', '20180724', '101500', '.00', '.00'), ('0100', '20180724', '100000', '.00', '.00'), ('0100', '20180724', '94500', '.00', '.00'), ('0100', '20180724', '93000', '.00', '.00'), ('0100', '20180724', '91500', '.00', '.00'), ('0100', '20180724', '90000', '.00', '.00'), ('0100', '20180724', '84500', '.00', '.00'), ('0100', '20180724', '83000', '.00', '.00'), ('0100', '20180724', '81500', '.00', '.00'), ('0100', '20180724', '80000', '.00', '.00'), ('0100', '20180724', '74500', '.00', '.00'), ('0100', '20180723', '253000', '986.07', '71.44'), ('0100', '20180723', '251500', '986.07', '71.44'), ('0100', '20180723', '250000', '986.07', '71.44'), ('0100', '20180723', '244500', '986.07', '71.44'), ('0100', '20180723', '243000', '986.07', '71.44'), ('0100', '20180723', '241500', '986.07', '71.44'), ('0100', '20180723', '240000', '986.07', '71.44'), ('0100', '20180723', '234500', '986.07', '71.44'), ('0100', '20180723', '233000', '986.07', '71.44'), ('0100', '20180723', '231500', '986.07', '71.44'), ('0100', '20180723', '230000', '986.07', '71.44'), ('0100', '20180723', '224500', '986.07', '71.44'), ('0100', '20180723', '223000', '986.07', '71.44'), ('0100', '20180723', '221500', '986.07', '71.44'), ('0100', '20180723', '220000', '986.07', '71.44'), ('0100', '20180723', '214500', '986.07', '71.44'), ('0100', '20180723', '213000', '986.07', '71.44'), ('0100', '20180723', '120000', '986.07', '71.44'), ('0100', '20180723', '210000', '986.07', '71.44'), ('0100', '20180723', '204500', '986.07', '71.44');

Edit #2: Using that sample data and running the query produces the 0.00 bug. But running SELECT * FROM trickledata WHERE Time = '120000 Produces a result of $212.63

Also my MariaDB version is 10.1.29

Edit #3: I tried the following:

''SELECT t.* FROM trickledata t
    -> JOIN (
    -> SELECT StoreID, MAX(Time) AS latest, Sales, Date
    -> FROM trickledata
    -> WHERE Date = '20180724'
    -> GROUP BY StoreID
    -> ) m ON t.StoreID = m.StoreID AND t.Time = m.latest;''

However, it returns every date i have at noon. I just want the data from today.

Edit #4: I figured out if i delete the Unique index, my initial query works as expected. But I don't understand why. Also I need that unique index to prevent duplicates from being added, as this is reading the information from a csv file.

Upvotes: 1

Views: 140

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Try to filter the time using a correlated subquery, that gets the maximum time for a day and store.

SELECT `t1`.`storeid` `StoreId`,
       `t1`.`time` `Last Reported`,
       `t1`.`sales` + `t1`.`tax` `Sales`,
       `t1`.`date` `Date`
       FROM `trickledata` `t1`
       WHERE `t1`.`date` = '2018-07-24'
             AND `t1`.`time` = (SELECT max(`t2`.`time`)
                                       FROM `trickledata` `t2`
                                       WHERE `t2`.`storeid` = `t1`.`storeid`
                                             AND `t2`.`date` = `t1`.`date`);

If there a duplicates (two or more reports on the same time for the same store on the same day) they will be shown. If you want to rule them out you have to define another criteria that should define an additional order on the reports.

Upvotes: 0

Related Questions