SQL compare avg for ProductID with exact value

Assume I have such table

OrderLineID  OrderID  ProductID  OrderedQunatity
      1        1          2           18
      2        1          10           9
      3        2          3           12
      4        2          8            2
      5        2          14           2
      7        4          3            1
      8        4          5            3
      9        5          6            2
      15       4          4            0

What I would like to do is compare every single OrderedQuantity with average orderedQuantity for exact product.

For example OrderedQuantity for OrderID = 2 and ProductID = 3 is equal to 12, so I check average OrderedQuantity for ProductID = 3, so (12+1)/2 = 6.5 and if it is smaller than exact orderQuantity (in this example 12) I select it.

Can someone help what should i type in SELECT?

Thank you!

Answer

One method is a correlated subquery:

select t.*
from t
where t.OrderedQuantity < (select avg(t2.OrderedQuantity)
                           from t t2
                           where t2.ProductID
                          );

Note that some databases do integer averages of integers. So you might need avg(t2.OrderedQuantity * 1.0) to get all values less than the average.

Advertisement

Answer