vehicle_assignment_history

id    companyAccountId          date           totalVan     totalBike
1            4           2021-11-11 00:00:00     2             0
2            4           2021-11-11 00:00:00     3             0
3            4           2021-11-11 00:00:00     1             0        
4            8           2021-11-11 00:00:00     1             0
5            8           2021-11-12 00:00:00     2             0        
6            9           2021-11-13 00:00:00     0             2
7            9           2021-11-14 00:00:00     0             1        

I want to calculate sum of each group last row of companyAccountId.also the date bewteen a range.

for example:- 2021-11-11 -> 2021-11-13

   totalVan              totalBike
1   2   0 = 3           0   0   2 = 2

2021-11-11 -> 2021-11-14

   totalVan              totalBike
1   2   0 = 3           0   0   1 = 1

CodePudding user response：

One way to do this is to take the max (for each companyAccountId) of a complex string that joins the id and the field you want to find for the highest id, then extract the field you want from the end and convert it back to a number (all in a subquery, so you can sum all the resulting values)

select sum(latestTotalVan) as totalVan, sum(latestTotalBike) as totalBike
from (
    select
        cast(substring(max(concat(lpad(id,11,'0'),totalVan)) from 12) as unsigned) latestTotalVan,
        cast(substring(max(concat(lpad(id,11,'0'),totalBike)) from 12) as unsigned) latestTotalBike
    from vehicle_assignment_history
    where date between '2021-11-11 00:00:00' and '2021-11-14 00:00:00'
    group by companyAccountId
) latest_values

fiddle

mysql 8 adds window functions that make this kind of thing much easier.

CodePudding user response：

SELECT companyAccountId, sum(totalVan) AS [Total Vans], sum(totalBike) AS [Total Bike], FROM vehicle_assignment_history GROUP BY companyAccountId HAVING '2021-11-11' < date AND date < '2021-11-13'