Issue in when I do sum of total time which is in varchar field

Question

0.00/5 (No votes)

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 :

SQL

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 1-Jun-20 2:42am

Member 10194266

Updated 1-Jun-20 10:53am

Maciej Los

v2

Add a Solution

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.

Member 10194266 2-Jun-20 1:44am

Thanks Richard

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 Dave

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2020-06-01T03:42:00

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.