Hi
I have a varchar field in sql table that store time (time like '04:10:28' this format) , i am stuck that how i can do sum of total time ,some other tables used in this query. what should be added in my query to correct this issue
field name is =T.Tripduration for time
After when i execute my query then some error msg is show
Msg 130, Level 15, State 1, Line 6<br />
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.<br />
<br />
Completion time: 2020-06-01T17:53:25.2400562+05:30
Thanks
What I have tried:
I have tired below query :
select O.OrgName,C.ClientName,V.vehicleregno,count(V.vehicleregno) as Trip_count, T.Tripduration,
sum(DATEDIFF(DAY, t.TripStartDate, t.TripEnddate)+1)as [Days_onTrip],
sum(case when t.UptimePer>100 then 100 else t.UptimePer end) /count(t.DeviceID )as UptimeDuringTrip ,
(25- sum(DATEDIFF(DAY, t.TripStartDate, t.TripEnddate)+1)) Truck_Idle_days ,'100' UptimeDuringIdling,
SUM(cast(cast(dateadd(MINUTE,SUM(DATEDIFF(MINUTE, '00:00:00', cast(convert(varchar(20),T.Tripduration ) as time))),'00:00:00') as time) as varchar(8)))
from tb_TripUptime t (nolock)
INNER join Client C(nolock) on C.ClientID=t.ClientID
INNER join Organization O (nolock) on O.OrgID=C.OrgID
inner join Vehicle V(nolock) on V.VehicleID=t.VehicleID
where O.OrgID =isnull(1,O.OrgID) and C.ClientID =isnull(null,C.ClientID) and t.vehicleid=isnull(null,t.vehicleid) and
TripEndDate between '2020-05-01 00:00:00' and '2020-05-25 23:59:59'
GROUP by O.OrgName,C.ClientName,V.vehicleregno,T.Tripduration