I have the following table:

``table: peopleid | name | income==========================1 Bob 102 John 53 Amy 154 Alyson 55 Henry 20``

I want to take the average of only a select number of rows, like this:

``SELECTid,name,(AVG(SELECT income FROM people WHERE FIND_IN_SET(id, '1,2,3')) - income) AS averageDiffFROM people;``

I expect to get a result like this:

``id | name | averageDiff==========================1 Bob 02 John 53 Amy -54 Alyson -55 Henry 10``

However, I get an error (#1064) when trying to use the SELECT clause inside of the AVG function. How can I do this?

Use this syntax:

``````SELECT avg(income) FROM people WHERE FIND_IN_SET(id, '1,2,3')
``````

You need to enclose the above query in brackeds in this way:

``````SELECT
......
(ABS(IFNULL(`age`, 0)
- IFNULL((SELECT AVG(age) FROM people WHERE FIND_IN_SET(id, '1,2,3')), 0)))
+ (ABS(IFNULL(`income`, 0)
- IFNULL((SELECT AVG(income) FROM people WHERE FIND_IN_SET(id, '1,2,3')), 0))) AS sumAvg
FROM `people`
....
``````

If you want the average for everybody as your starting point, calculate that with a query and cross join it to the people you want to include:

``````SELECT
people.id,
people.name,
people.income - av.avgincome AS averageDiff
FROM people
CROSS JOIN (SELECT AVG(income) AS avgincome FROM people) av
WHERE people.ID IN (1, 2, 3)
``````

If you want the average for the subset of people with ID 1, 2 or 3 as your starting point you can do it like this:

``````SELECT
people.id,
people.name,
people.income - av.avgincome AS averageDiff
FROM people
CROSS JOIN (
SELECT AVG(income) AS avgincome
FROM people
WHERE ID IN (1, 2, 3)) av
WHERE people.ID IN (1, 2, 3)
``````

Both approaches avoid the correlated subquery (meaning a `SELECT` for a column name based on the top-level table), which is slow with large recordsets.

The `FIND_IN_SET(people.id, '1,2,3') will work, but if you have an index on`table.id`the`IN (1, 2, 3)` will be much faster. It will probably be faster even if you don't have the index.

Top