using AVG aggregate function in sql server 2005

Question

Hi I have one "orders" table:

ID  CustomerID  Freight
1      VINET      32.38
2      TOMPS      11.61
.        .          .
.        .          . 
.        .          .

I want to select just those customerID s that have freight more than average!!

SELECT CustomerID FROM Orders WHERE CustomerID IN(SELECT CustomerID,AVG(Freight) AVGFreight FROM Orders Group By CustomerID )

I know till here but how can I compare freight with the average!?? please help me thanks

Answer 1

How about this:

SELECT CustomerID 
FROM dbo.Orders 
WHERE Freight >= (SELECT AVG(Freight) FROM dbo.Orders)
GROUP BY CustomerID

You select your customers, all of which have a freight cost which is larger than the average of all freight costs in the table.

Answer 2

I am assuming that a customer will have 1 record each.

SELECT CustomerID FROM dbo.Orders WHERE Freight >= (SELECT @AvgFreight = AVG(Freight) FROM dbo.Orders)

I am not sure, if this will work. Try it.
Upvote @marc_s's answer, as I am basing it off his reply.

SELECT CustomerID
FROM dbo.Orders 
WHERE AVG(Freight) >= (SELECT AVG(Freight) FROM dbo.Orders)
GROUP BY CustomerID