CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot-table
Indra
Indra

Reputation: 279

How to get Average from two columns using 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
  4            2           7       15      60
  5            2           6       30      60

Result should be SUM(price*Quantity)/SUM(Quantity) as Average for Same size.

HeaderID     Average-50     Average-60 
  1                 9            4.7

Price is per 1 quantity* from above table, I want to get average price of Size '50' using PIVOTE.

Upvotes: 0

Views: 1881

Answers (2)

Taryn
Taryn

Reputation: 247700

If you want to PIVOT the result, then you can use something similar to this:

select *
from
(
  select headerid, size,
    sum(price*quantity)/sum(quantity*1.0) perUnit
  from yourtable
  group by headerid, size
) src
pivot
(
  avg(perUnit)
  for size in ([50], [60])
) piv

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

select sum(price * quantity) / sum(quantity)
  from tbl
 where size = 50;

-- result
9.0

For all sizes:

  select size, sum(price * quantity) / sum(quantity) avg_price_for_size
    from tbl
group by size
order by size;

Upvotes: 0

Related Questions