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.

Answer 1

Does the following work for you?

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

AVG will ignore your NULL entry however.