0

I don't know how I am missing this, but I am sure it's from late nights! Any help appreciated let's say we are using NorthWind to Calculate Weighted AVG

USE NORTHWIND
Select  OD.UnitPrice,OD.Quantity,
sum(OD.UnitPrice*OD.Quantity)/sum(OD.Quantity) OVER (PARTITION BY
OD.UnitPrice, OD.Quantity) as[ W-AVERAGE] 
 From [Order Details] OD

What am I missing as to why SQL keeps saying Column 'Order Details.UnitPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.? I thought using Partition By solves having to have a group by?

philbeat
  • 53
  • 9

1 Answers1

4

Resolved: You MUST use the partition by in EVERY aggregate, the error msg is a bit misleading, but just remember whenever you use PArtition by it must be used in every instance of any Aggregate Functions!

           Select OD.UnitPrice,OD.Quantity,
           sum(OD.UnitPrice*OD.Quantity) Over (PARTITION BY OD.UnitPrice,  
           OD.Quantity)/sum(OD.Quantity) OVER (Partition By OD.Quantity) as[W-AVERAGE] 
           From [Order Details] OD
philbeat
  • 53
  • 9