Tutorial :TSQL Average Column By Distinct Entries In Another Column



Question:

I start with a table looking like this...

  +-------------------------+  | procName | TimeEnded    |  +-------------------------+  |        A | 04:00:00.000 |  |        B | 04:01:00.000 |  |        C | 04:03:00.000 |  |        A | 04:06:00.000 |  |        B | 04:10:00.000 |  |      ... |          ... |  

Run a query to generate a RunTime column, making it look like this...

  +-------------------------+--------------+  | procName | TimeEnded    |      RunTime |  +-------------------------+--------------+  |        A | 04:00:00.000 |         NULL |  |        B | 04:01:00.000 | 00:01:00.000 |  |        C | 04:03:00.000 | 00:02:00.000 |  |        A | 04:06:00.000 | 00:03:00.000 |  |        B | 04:10:00.000 | 00:04:00.000 |  |      ... |          ... |          ... |  

and want to average the RunTime column for each distinct entry in the procName column, for something like this:

  +-------------------------+  | procName |   AvgRunTime |  +-------------------------+  |        A | 00:03:00.000 |  |        B | 00:02:30.000 |  |        C | 00:02:00.000 |  |      ... |          ... |  

How would I go about doing this? You can ignore the table1->table2 step, I just included it for some background information to describe my problem.

Basically how can I average the data in the RunTime column, based on distinct entries in the procName column?

Thanks.


Solution:1

Does the following work for you?

SELECT procName, AVG(RunTime)  FROM table  GROUP BY procName   

AVG will ignore your NULL entry however.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »