January 28, 2021 at 9:44 am
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':
See here: http://sqlfiddle.com/#!18/2636c/18
January 28, 2021 at 10:24 am
You can use OUTER APPLY
SELECT [Car type] = C.CarType
, Maintenance = SUM( mv.MaintenanceCost )
, Fuel = SUM( fv.Quantity )
, Distance = SUM( fv.Distance )
, [L/100km (Avg)] = AVG( fv.[L/100km] )
, [L/100km (Total)] = ( SUM( fv.Quantity ) * 100 ) / SUM( fv.Distance )
FROM Cars AS C
OUTER APPLY (
SELECT Quantity = SUM( F.Quantity )
, Distance = ( MAX( F.Odometer ) - MIN( F.Odometer ))
, [L/100km] = ( SUM( F.Quantity ) * 100 ) / ( MAX( F.Odometer ) - MIN( F.Odometer ))
FROM Fuelings AS F
WHERE F.CarID = C.CarID
AND F.FuelingDate BETWEEN '2021-01-01' AND '2021-01-31'
) AS fv
OUTER APPLY (
SELECT MaintenanceCost = SUM( M.MaintenanceCost )
FROM Maintenances AS M
WHERE M.CarID = C.CarID
AND M.MaintenanceDate BETWEEN '2021-01-01' AND '2021-01-31'
) AS mv
GROUP BY C.CarType;
January 29, 2021 at 11:41 am
was apply available on sql 2005?
January 29, 2021 at 12:57 pm
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy