SUM multiple tables GROUP BY column

Question

Post reply

SUM multiple tables GROUP BY column

milo1981

Old Hand

Points: 375

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

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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: 24275 More actions · Answer 2

This is a duplicate post.

Original HERE

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