Click here to Skip to main content
14,574,905 members
Rate this:
Please Sign up or sign in to vote.
See more:
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(T.Tripduration AS TIME))),'00:00:00')AS TIME)AS VARCHAR(8)) 
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
Posted
Updated 1-Jun-20 10:53am
v2
Comments
ZurdoDev 1-Jun-20 8:54am
   
Is the time stored as Time, DateTime, or varchar?
Member 10194266 1-Jun-20 9:00am
   
time is stored in varchar field that is Tripduration
value example=2.07:53 in my table
Richard MacCutchan 1-Jun-20 9:20am
   
Then you need to change your database so it is stored as a Time or DateTime type. Storing dates and times as strings makes life very difficult for yourself.
Member 10194266 1-Jun-20 9:35am
   
Hi Richard ,
For this table i can't change the data type, any other option ?
Richard MacCutchan 1-Jun-20 11:02am
   
Yes, redesign your database. As I said, you are just storing up trouble for the future by not using the correct datatypes.
Dave Kreskowiak 1-Jun-20 21:20pm
   
By storing all of your times in a string column instead of using a date/time column, you've guaranteed any math you do on the column will require extra time to compute. This is because the database has to do a conversion on every row you're trying to do math on. The more rows you have, the longer the math is going to take. You're adding an unnecessary overhead to the operation.
Member 10194266 2-Jun-20 1:44am
   
Thanks Richard
Member 10194266 2-Jun-20 1:44am
   
Thanks Dave

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Change your database: alway store values in the most appropriate datatype, not VARCHAR / NVARCHAR.

Store dates in DATE, DATETIME, or DATETIME2; store money in DECIMAL, store counters in INT, and so on.
If you want to add two times together, then store them in an INT or BIGINT column - and make them the number of seconds since midnight. You can then add the, subtract them, average them - whatever you need - and convert the result to a "human readable time" when the result is complete.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100