Procedure           Intermediate      New       Old      Total   avg
   Proc1                       6        0         0        6     2.000000
   Proc2                      74       13         0       87     29.000000
   Proc3                      29        0         0       29     9.666666
   Proc4                      16        0         0       16     5.333333

;with cte as
(
    select
        f.Procedure,
        SUM(case when f.old_new = 'O' then f.Value else 0 end) "Intermediate",
	SUM(case when f.old_new = 'Y' then f.Value else 0 end) "New",
	SUM(case when f.old_new = 'N' then f.Value else 0 end) "Old"
	from MyTable f
	group by f.Procedure
)
select cte.Procedure,
       cte.[Old],
       cte.[Intermediate],
       cte.[New],
       coalesce(cte.[Intermediate], 0) + coalesce(cte.[Old], 0) + coalesce(cte.[New], 0) Total,
       avg(Value) [avg]
from cte
cross apply
(
    select cast(Intermediate] as decimal(10, 2)) union all 
    select cast([New] as decimal(10, 2)) union all
    select cast([Old] as decimal(10, 2))
) c(Value)  
group by Procedure, [Intermediate], [New], [Old]

quote:

---

Originally posted by TG

>>I have this data returning from a query
If you post that query we may be able to modify it to return the results you want to end up with.

>>I got this code at another site but wanted to see what the you SQL guys think I need to do here?
Does it work for you?
Looks to me like they missed using a: "cast([NULLIF,[Intermediate],0) as decimal(10, 2)) union all" around the 3 columns in the cross apply statement.

There's other ways you could do this but unless you are getting bad results or bad performance why bother changing it?

Be One with the Optimizer
TG

---

Warning: Null value is eliminated by an aggregate or other SET operation.

set ansi_warnings off

quote:

---

Originally posted by TG

>>Would nullif make any difference to the averages, would this only calculate the true values not include 0
That is why I added it.
Sql server will exclude nulls from aggregates but not zeros.

so:
avg(2,2,0,0) = 1
vs.
avg(2,2,null,null) = 2


EDIT:

if NULLs are included in the aggregate you will likely see this message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If that message bother you you can suppress it with:

set ansi_warnings off

That SET will only affect the current user session.

Be One with the Optimizer
TG

---