using AVG aggregate function in sql server 2005

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 开发者_JAVA技巧compare freight with the average!?? please help me thanks

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

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.

CREATE TABLE Orders(
    ID int,
    CustomerId varchar(10),
    Freight money
);
INSERT INTO Orders Values(1,'VINET',10);
INSERT INTO Orders values(2,'VINET',10);
INSERT INTO Orders values(3,'TOMPS',12);
INSERT INTO Orders values(4,'TOMPS',11);

Display average:

Select CustomerId,avg(Freight)AvgFreight
from Orders
group by customerId

| CustomerId | AvgFreight | |------------+------------| | TOMPS | 11.50 | | VINET | 10.00 | |------------+------------|

Compare freight with the average:

Select Orders.CustomerId 
from Orders,
(Select CustomerId,avg(Freight)AvgFreight
from Orders
Group by customerId)t
Where Orders.Freight > t.AvgFreight
And Orders.CustomerId = t.Customerid

| CustomerId | |------------| | TOMPS | |------------|

Using HAVING clause.

SELECT CustomerID
FROM Orders 
GROUP BY CustomerID 
HAVING AVG(Freight) > (SELECT AVG(Freight) FROM Orders)