SUM multiple tables GROUP BY column

  • I have the following SQL Server 2005 tables:

    create table Cars
    CarID int,
    CarType varchar(50),
    PlateNo varchar(20)

    create table Fuelings
    CarID int,
    FuelingDate date,
    Odometer int,
    Quantity decimal,
    Cost money

    create table Maintenances
    CarID int,
    MaintenanceDate date,
    MaintenanceCost money

    I'm having problems calculating the fuel consumption grouped by the column CarType. To get the fuel consumption I want to calculate the distance and fuel quantity for each car, then sum that up grouped by the column CarType.

    What I have now:

    SELECT DISTINCT C.CarType AS [Car type],
    SUM(M.MaintenanceCost) AS [Maintenance],
    SUM(F.Cost) AS [Fuel],
    (MAX(Odometer)-MIN(Odometer)) AS [Distance],
    (SUM(Quantity)*100)/(MAX(Odometer)-MIN(Odometer)) AS [L/100km]
    FROM Cars AS C
    LEFT JOIN Maintenances AS M ON M.CarID=C.CarID
    AND M.MaintenanceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
    LEFT JOIN Fuelings AS F ON F.CarID=C.CarID
    AND F.FuelingDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
    GROUP BY C.CarType

    Desired result for type 'SUV':

    • Total fuel quantity: 301
    • Total distance: 1600
    • Consumption: 18,8125

    See here:!18/2636c/18

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This is a duplicate post.

    Original HERE


Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply