SQLTeam.com | Weblogs | Forums

Combining AVG and SUM functions


#1

I have a derived column in my stored procedure where i process the amount of days in an ordering process

[ORDER PROCESS] = SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped,121),getdate()) -
(2 * DATEDIFF(wk,convert(datetime,orders.orderarrived,121),getdate()))) < 2 AND orders.closed <> '' THEN 1 ELSE 0 END)

but i need to find the average amount of days ; when i do ....AVG(SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped.... it is not legal syntax as I cannot combine average and sum aggregate functions. Is there another trick for this?
Thanks


#2

Since avg is the sum / count, you could do this:

[ORDER PROCESS] = SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped,121),getdate()) -
(2 * DATEDIFF(wk,convert(datetime,orders.orderarrived,121),getdate()))) < 2 AND orders.closed <> '' THEN 1 ELSE 0 END),
[ORDER AVERAGE] = SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped,121),getdate()) -
(2 * DATEDIFF(wk,convert(datetime,orders.orderarrived,121),getdate()))) < 2 AND orders.closed <> '' THEN 1 ELSE 0 END) / NULLIF(COUNT(orders.orderarrived), 0)