SUM multiple tables GROUP BY column

• milo1981

  Old Hand

  Points: 375

  More actions

  January 28, 2021 at 10:05 am

  #3838292

  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: http://sqlfiddle.com/#!18/2636c/18

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  January 29, 2021 at 10:10 am

  #3838667

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

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

• DesNorton

  SSC-Insane

  Points: 24512

  More actions

  January 29, 2021 at 10:57 am

  #3838681

  This is a duplicate post.

  Original HERE

   

  ---

  How to post data/code on a forum to get the best help.