SUM multiple tables GROUP BY column

Question

Post reply

SUM multiple tables GROUP BY column

milo1981

Old Hand

Points: 375

January 28, 2021 at 9:44 am

#3838289

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 4 posts - 1 through 3 (of 3 total)

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

DesNorton SSC-Insane Points: 24401 More actions · Answer 1

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;

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

frederico_fonseca SSCoach Points: 16167 More actions · Answer 2

frederico_fonseca

SSCoach

Points: 16167

January 29, 2021 at 11:41 am

#3838688

was apply available on sql 2005?

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

I believe that APPLY was introduced in 2005

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

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