Averaging values into the same output

[NHW]

Hi, I'm having troubles trying to aggregate some data. The source table is like this:

Staff Shop Colour Value
----- ---- ------ -----
1 1 R 50
2 1 R 35
3 1 R 44
1 2 R 70
2 2 R 34
1 1 B 90
2 1 B 34
1 2 B 56
3 2 B 23
1 3 B 45
2 3 B 23
3 3 B 34


And I'd like to create an output (can be just in Query Analyzer) of the average "Value" of each "Colour" aggregated by "Shop" like this:

Shop Avg_R Avg_B
---- ----- -----
1 43 62
2 52 39.5
3 [NULL] 34

Thanks a mil!

NHW

 

[vongrunt]

One way: get averages for red, get averages for blue, full join both sets:

select isnull(R.Shop, B.Shop) as Shop, R.Avg_R, B.Avg_B
from
(	select Shop, Avg(value) as Avg_R
	from blah
	where Color = 'R'
	group by Shop
) R
full outer join
(	select Shop, Avg(value) as Avg_B
	from blah
	where Color = 'B'
	group by Shop
) B
on R.Shop = B.Shop
order by Shop

That becomes clunky with more than 3 sets (colors), so you may also consider something like:

set ansi_warnings off

select Shop, 
	Avg( case when color = 'R' then value end) as Avg_R, 
	Avg( case when color = 'B' then value end) as Avg_B
from blah
group by Shop
order by Shop

set ansi_warnings on

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NHW]

Thanks. It works like a charm