Only include values from the latest timestamp into the query result

Question

DB-Fiddle

CREATE TABLE sales (
    id int auto_increment primary key,
    time_stamp TIMESTAMP,
    product VARCHAR(255),
    sales_quantity INT
);

INSERT INTO sales
(time_stamp, product, sales_quantity)
VALUES 
("2020-01-14 07:15:30", "Product_A", "100"),
("2020-01-14 07:15:30", "Product_B", "300"),
("2020-01-14 07:18:45", "Product_A", "200"),
("2020-01-14 07:18:45", "Product_B", "900"),

("2020-01-15 07:19:23", "Product_A", "400"),
("2020-01-15 07:19:23", "Product_B", "270"),
("2020-01-15 07:45:10", "Product_A", "900"),
("2020-01-15 07:45:10", "Product_B", "340");

Expected Restult:

time_stamp             sales_quantity
2020-01-14              1.100
2020-01-15              1.240

---

As you can see in the table there are multiple TIMESTAMP per day.
Now, I want to query the sum of the sales_quantity for each TIMESTAMP but only the lates ones should be included.

Therefore, in the example the TIMESTAMP with 07:15:30 and 07:19:23 should be ignored.

I tried to go with this query:

SELECT
MAX(time_stamp),
SUM(sales_quantity) AS sales_quantity
FROM sales
GROUP BY 1;

However, I get the error Can't group on 'MAX(time_stamp)'.
How do I need to modify the query to get the expected result?

Answer 1

You need a condition with NOT EXISTS in the WHERE clause so that you aggregate only the rows with the latest time_stamp of each day:

SELECT DATE(s.time_stamp) time_stamp,
       SUM(s.sales_quantity) sales_quantity
FROM sales s
WHERE NOT EXISTS (SELECT 1 FROM sales WHERE DATE(time_stamp) = DATE(s.time_stamp) AND time_stamp > s.time_stamp)
GROUP BY DATE(s.time_stamp);

See the demo.

If you are using MySql 8.0+ and not MariaDB 10.3 (like your fiddle) then you could also do it with FIRST_VALUE() window function:

SELECT DISTINCT
       DATE(s.time_stamp) time_stamp,
       FIRST_VALUE(SUM(s.sales_quantity)) OVER (PARTITION BY DATE(s.time_stamp) ORDER BY s.time_stamp DESC) sales_quantity
FROM sales s
GROUP BY s.time_stamp;

See the demo.

Results:

> time_stamp | sales_quantity
> :--------- | -------------:
> 2020-01-14 |           1100
> 2020-01-15 |           1240