Correct Nested Query

user_3804983 0
Viewed 67

When I run the following T-SQL

use xxx

select      
    t.Vehicle,
    t.Distance,
    t.FuelConsumption,
    d.LastConnection,
    v.Make,
    v.Model
from
    dbo.Trips t
left join   
    dbo.Vehicles v on v.Id = t.Vehicle
left join   
    dbo.Devices d on d.Id = v.DeviceId
where       
    t.Date > '2020-03-02' and Distance > 1
order by    
    t.Vehicle, t.FuelConsumption

I get 34 rows as result that look like this:

enter image description here

The first Vehicle ID 76 has done 2 trips, 1 recorded fuel the other didn't. This is what I'm trying to establish.

So I attempted the following nested query

 select     
     t.Vehicle,
     d.LastConnection,
     v.Make,
     v.Model,
     count(t.id) as TripCount,
     sum(NoFuelRecord) as NoFuelRecord,
     sum(FuelRecorded) as FuelRecorded          
from
    (select count(Id) as NoFuelRecord
     from dbo.Trips 
     where Distance > 1 and FuelConsumption <= 0 and Date > '2020-03-02'
     group by Vehicle)  as NoFuelRecord,
    (select count(Id) as FuelRecorded
     from dbo.Trips 
     where Distance > 1 and FuelConsumption > 0 and Date > '2020-03-02'
     group by Vehicle) as FuelRecorded,
    dbo.Trips t         
left join   
    dbo.Vehicles v on v.Id = t.Vehicle
left join   
    dbo.Devices d on d.Id = v.DeviceId
where
    t.Date > '2020-03-02' and Distance > 1
group by
    t.Vehicle, v.Make, v.Model, d.LastConnection
order by    
    t.Vehicle

Which returned the following results:

enter image description here

So what I'm expected to see in row 1 is TripCount: 2, NoFuelRecord: 1, FuelRecorded: 1

I'm not even close! How do I do this please?

nested-query tsql sql-server sql
1 Answers

It is hard to track why your query is not returning the expected output. But based on your initial query and the expected results that you described, this should give you what you are after

WITH CTE as (
SELECT      t.Vehicle,
            t.Distance,
            t.FuelConsumption,
            t.Id,
            d.LastConnection,
            v.Make,
            v.Model
FROM        dbo.Trips t
LEFT JOIN   dbo.Vehicles v on v.Id = t.Vehicle
LEFT JOIN   dbo.Devices d on d.Id = v.DeviceId
WHERE       t.Date > '2020-03-02' and Distance > 1
)
SELECT Vehicle,
            LastConnection,
            Make,
            Model,
            COUNT(Id) AS TripCount,
            SUM(CASE WHEN FuelConsumption > 0 THEN 1 ELSE 0 END) AS FuelRecorded,
            SUM(CASE WHEN FuelConsumption <= 0 THEN 1 ELSE 0 END) AS NoFuelRecorded 
FROM CTE 
GROUP BY  Vehicle,
            LastConnection,
            Make,
            Model
user_27932280
Related