CODEWITHSUNDEEP
sqlsql-server-2008
Rikky
Rikky

Reputation: 101

SQL Server 2008 - calculation of avg. timing

I have a query

select
  *,
  right(
    convert(varchar, cast(JOB_DONE_time-JOB_send_time as datetime), 121),
    12
  ) [TimeTaken]
from dbo.insert_data
order by job_id desc

and result of this query is like

JOB_ID     JOB_DONE_TIME            JOB_SEND_TIME            TimeTaken
---------  -----------------------  -----------------------  ------------
001-99900  2011-12-28 13:36:57.860  2011-12-28 13:36:57.593  00:00:00.267
001-99800  2011-12-28 13:36:57.843  2011-12-28 13:36:57.593  00:00:00.250
001-99700  2011-12-28 13:36:57.687  2011-12-28 13:36:57.547  00:00:00.140
001-99600  2011-12-28 13:36:57.593  2011-12-28 13:36:57.547  00:00:00.047
001-99500  2011-12-28 13:36:57.563  2011-12-28 13:36:57.437  00:00:00.127
001-99400  2011-12-28 13:36:57.547  2011-12-28 13:36:57.437  00:00:00.110
001-99300  2011-12-28 13:36:57.483  2011-12-28 13:36:57.377  00:00:00.107
001-99200  2011-12-28 13:36:57.437  2011-12-28 13:36:57.377  00:00:00.060
001-99100  2011-12-28 13:36:57.407  2011-12-28 13:36:57.187  00:00:00.220
001-99000  2011-12-28 13:36:57.360  2011-12-28 13:36:57.187  00:00:00.173
001-9900   2011-12-28 13:32:22.657  2011-12-28 13:32:22.500  00:00:00.127

and by this query I have got TimeTaken column. Now I want to calculate addition and avg. of column timetaken. I have tried with SUM() function but it gives me error:

Msg 8117, Level 16, State 1, Line 1 Operand data type varchar is invalid for sum operator.

How can I calculate it???

Upvotes: 3

Views: 662

Answers (4)

Andriy M
Andriy M

Reputation: 77707

As you've already been told, the issue has to do with the fact that you are trying to apply AVG() or SUM() to a string column, while in fact you can only use those functions on numeric columns.

As a very SQL Server-specific solution, you could temporarily cast the result of JOB_DONE_time - JOB_send_time to float, calculate AVG() and/or SUM() then convert the results back to datetime, then to varchar, then extract the last 12 characters, like you are doing now on the non-aggregated results of the subtraction.

Here's an example of what I mean:

SELECT
  JOB_ID,
  JOB_DONE_TIME,
  JOB_SEND_TIME,
  TimeTaken      = RIGHT(CONVERT(varchar(30), CAST(    TimeTakenFP          AS datetime), 121), 12),
  AvgTimeTaken   = RIGHT(CONVERT(varchar(30), CAST(AVG(TimeTakenFP) OVER () AS datetime), 121), 12),
  TotalTimeTaken = RIGHT(CONVERT(varchar(30), CAST(SUM(TimeTakenFP) OVER () AS datetime), 121), 12)
FROM (
  SELECT
    *,
    TimeTakenFP = CAST(JOB_DONE_TIME - JOB_SEND_TIME AS float)
  FROM insert_data
) s
;

For your sample data, the above query returns the following result set:

JOB_ID    JOB_DONE_TIME           JOB_SEND_TIME           TimeTaken    AvgTimeTaken TotalTimeTaken
--------- ----------------------- ----------------------- ------------ ------------ --------------
001-99900 2011-12-28 13:36:57.860 2011-12-28 13:36:57.593 00:00:00.267 00:00:00.150 00:00:01.657
001-99800 2011-12-28 13:36:57.843 2011-12-28 13:36:57.593 00:00:00.250 00:00:00.150 00:00:01.657
001-99700 2011-12-28 13:36:57.687 2011-12-28 13:36:57.547 00:00:00.140 00:00:00.150 00:00:01.657
001-99600 2011-12-28 13:36:57.593 2011-12-28 13:36:57.547 00:00:00.047 00:00:00.150 00:00:01.657
001-99500 2011-12-28 13:36:57.563 2011-12-28 13:36:57.437 00:00:00.127 00:00:00.150 00:00:01.657
001-99400 2011-12-28 13:36:57.547 2011-12-28 13:36:57.437 00:00:00.110 00:00:00.150 00:00:01.657
001-99300 2011-12-28 13:36:57.483 2011-12-28 13:36:57.377 00:00:00.107 00:00:00.150 00:00:01.657
001-99200 2011-12-28 13:36:57.437 2011-12-28 13:36:57.377 00:00:00.060 00:00:00.150 00:00:01.657
001-99100 2011-12-28 13:36:57.407 2011-12-28 13:36:57.187 00:00:00.220 00:00:00.150 00:00:01.657
001-99000 2011-12-28 13:36:57.360 2011-12-28 13:36:57.187 00:00:00.170 00:00:00.150 00:00:01.657
001-9900  2011-12-28 13:32:22.657 2011-12-28 13:32:22.500 00:00:00.157 00:00:00.150 00:00:01.657

This query calculates the aggregated results using windowed aggregate functions, so that you could see both aggregated and non-aggregated results. I only used windowed functions because I wanted to show you how your method of displaying the time can be applied to both non-aggregated and aggregated results, so that you could see which part of the expression changes depending on what kind of the result you need to convert to the time representation. In reality you may use "normal" aggregated results (and grouping, if necessary), if that's your requirement.

You can play with a "live" demonstration of the query at SQL Fiddle.

Upvotes: 1

CapelliC
CapelliC

Reputation: 60034

I think (sorry, can't try out now) that you need DATEDIFF.

In your case:

select avg(DATEDIFF(Millisecond, JOB_DONE_time, JOB_send_time)) from dbo.insert_data

Upvotes: 0

rene
rene

Reputation: 42483

You can determine the datediff in milliseonds for the done and send time columns and then avg or sum those results

select sum(datediff(ms, JOB_send_time, JOB_DONE_time)) as SUM_Timing
,      avg(datediff(ms, JOB_send_time, JOB_DONE_time)) as AVG_Timing
from   dbo.insert_data

Upvotes: 0

user1102001
user1102001

Reputation: 707

use avg function in sql which will give you avg for column

SELECT AVG(column name) FROM tabelname;

and for sum use like this

 SELECT sum(column name)  FROM tabelname;

Upvotes: 0

Related Questions