How would I write a query that would select a group of records and give me the average value of one of the fields?

SELECT ID, Name, Amount
FROM tblCustomers
WHERE Amount > 10

Assuming the query found 100 qualifying records what would the statements look like that would give me the average value of the 'Amount' field? 

Thank you

Found the AVG function -

SELECT AVG(Amount)
FROM tblNAME

hi,

i think you want this way or not

SELECT ID, Name, AVG(Amount) as AvgAmount
FROM tblCustomers
WHERE Amount > 10

Hi,

Try this

DECLARE  @tblCustomers TABLE
(
ID INT IDENTITY, NAME VARCHAR(20), Amount INT
)



INSERT INTO @tblCustomers
SELECT 'Name1',100 UNION ALL
SELECT 'Name2',200 UNION ALL
SELECT 'Name3',300 UNION ALL
SELECT 'Name4',400 


;WITH CTE AS
(
SELECT Amount
FROM @tblCustomers
WHERE Amount > 100
)

SELECT AVG([Amount]) AS [AVERAGEAMOUNT] FROM CTE

--OUTPUT
-------------
300

vipuldonga:

hi,

i think you want this way or not

SELECT ID, Name, AVG(Amount) as AvgAmount
FROM tblCustomers
WHERE Amount > 10

In order for this query to work you need to add GROUP BY ID,NAME - otherwise you'll get an error in this query.

If you're using SQL Server 2005 or up, you can add an aggregate result directly into this query, e.g.

 SELECT ID, Name, Amount, AVG(amount) OVER ()
FROM tblCustomers
WHERE Amount > 10