question

How to get average of two columns in PIVOT?

In my table i want to get the average value of two columns PackerID HeaderID Price Quantity Size 1 1 10 10 50 2 1 10 10 60 3 1 8.5 20 50 Price is per 1 quantity* from above table i want to get average price of Size '50' using PIVOTE.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
If there are 1000+ PackerID's then how it is posible?
0 Likes 0 ·
·
Any solution should work for 1 row, 10 rows or 1000+ rows - what issue specifically do you have?
0 Likes 0 ·

·
If you look at the [PIVOT documentation][1], you'll see that one of the examples is an AVERAGE pivot. If you can build an intermediate table with the unpivoted data, then feed that into the PIVOT, you should be fine. If you want more help, then you'll need to explain exactly what you want and how you want it calculated... [1]: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why use `pivot`? I can get the average per size using a aggregate windowing function declare @t table (packerid int, price decimal(8,2), quantity int, size int) insert into @t select 1,10,10,50 insert into @t select 2,10,10,60 insert into @t select 3,8.5,10,50 select distinct size, avg(price)over(partition by size) from @t gives 50 9.250000 60 10.000000
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Yeah, that was going to be another question, but I'm feeling a bit manflu-y, so didn't remember to write that in... ...but, in my defence, I did ask for full details of what he wanted to do.
0 Likes 0 ·