Need to aggregate latest product prices of all products from batchTransaction Table, relevant Columns:
id
– UniqueproductId
– Not uniquetransactionValue
– Value of that transactiontransactionDate
– date of that transaction
A product can have multiple transactions but only latest needs to be considered for aggregation. Need to aggregate total transactionValue across plant at a provided date, for all products.
SELECT SUM(transactionQuantity)
FROM batchTransaction
WHERE (id, dateCreated) IN (
SELECT id, MAX(dateCreated)
FROM batchTransaction
WHERE AND transactionDate < 1675189800000
GROUP BY productId
);
Above query would have worked, but it gives error – this is incompatible with sql_mode=only_full_group_by
2
Answers
The only way this makes sense with your description of getting the latest transaction per product is to group by
productId
in the subquery, and useproductId
in the result. Then compare that to theproductid
in the outer query.I also removed a superfluous
AND
keyword from your subquery.I assume from this that
transactionDate
is stored as aBIGINT
representing the UNIX timestamp in milliseconds, not as aDATETIME
type.A more modern way to write this sort of query is to use a window function
ROW_NUMBER()
and select only those that are the first (latest) row in each partition byproductId
.This syntax requires MySQL 8.0 for the window function.
Avoid IN clause as it causes performance issue when run on a larger dataset. Join would be a better option in such scenarios.
The id in the IN clause, is not guaranteed that it is of latest transaction because when you do group by product id, records with same product id are grouped and the order of those records is not maintained as you are assuming.
Query to achieve the right results