question

Shaker avatar image
Shaker asked

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.
sql-server-2008pivot
2 comments
10 |1200 characters needed characters left characters exceeded

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 ·
ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

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

Kev Riley avatar image
Kev Riley answered
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
10 |1200 characters needed characters left characters exceeded

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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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