Unknown column in mysql subquery

I'm not an MySQL expert (in MS SQL it could be done easier), and your question looks a bit unclear for me, but it looks like you're trying to get average of previous 5 items.

If you have Id without gaps, it's easy:

select    p.id,    (        select avg(t.deposit)        from products as t        where t.itemid = 1 and t.id >= p.id - 5 and t.id < p.id    ) as avgdepositfrom products as pwhere p.itemid = 1order by p.id desclimit 15

If not, then I've tri tried to do this query like this

select    p.id,    (        select avg(t.deposit)        from (            select tt.deposit            from products as tt            where tt.itemid = 1 and tt.id < p.id            order by tt.id desc            limit 5        ) as t    ) as avgdepositfrom products as pwhere p.itemid = 1order by p.id desclimit 15

But I've got exception Unknown column 'p.id' in 'where clause'. Looks like MySQL cannot handle 2 levels of nesting of subqueries.But you can get 5 previous items with offset, like this:

select    p.id,    (        select avg(t.deposit)        from products as t        where t.itemid = 1 and t.id > coalesce(p.prev_id, -1) and t.id < p.id    ) as avgdepositfrom (    select        p.id,        (            select tt.id            from products as tt            where tt.itemid = 1 and tt.id <= p.id            order by tt.id desc            limit 1 offset 6        ) as prev_id    from products as p    where p.itemid = 1    order by p.id desc    limit 15) as p

sql fiddle demo

This is my solution. It is easy to understand how it works, but at the same time it can't be optimized much since I'm using some string functions, and it's far from standard SQL. If you only need to return a few records, it could be still fine.

This query will return, for every ID, a comma separated list of previous ID, ordered in ascending order:

SELECT p1.id, p1.itemid, GROUP_CONCAT(p2.id ORDER BY p2.id DESC) previous_idsFROM  products p1 LEFT JOIN products p2  ON p1.itemid=p2.itemid AND p1.id>p2.idGROUP BY  p1.id, p1.itemidORDER BY  p1.itemid ASC, p1.id DESC

and it will return something like this:

| ID | ITEMID |      PREVIOUS_IDS ||----|--------|-------------------|| 10 |      1 | 9,8,7,6,5,4,3,2,1 ||  9 |      1 |   8,7,6,5,4,3,2,1 ||  8 |      1 |     7,6,5,4,3,2,1 ||  7 |      1 |       6,5,4,3,2,1 ||  6 |      1 |         5,4,3,2,1 ||  5 |      1 |           4,3,2,1 ||  4 |      1 |             3,2,1 ||  3 |      1 |               2,1 ||  2 |      1 |                 1 ||  1 |      1 |            (null) |

then we can join the result of this query with the products table itself, and on the join condition we can use FIND_IN_SET(src, csvalues) that return the position of the src string inside the comma separated values:

ON FIND_IN_SET(id, previous_ids) BETWEEN 1 AND 5

and the final query looks like this:

SELECT  list_previous.id,  AVG(products.deposit)FROM (  SELECT p1.id, p1.itemid, GROUP_CONCAT(p2.id ORDER BY p2.id DESC) previous_ids  FROM    products p1 INNER JOIN products p2    ON p1.itemid=p2.itemid AND p1.id>p2.id  GROUP BY    p1.id, p1.itemid  ) list_previous LEFT JOIN products  ON list_previous.itemid=products.itemid     AND FIND_IN_SET(products.id, previous_ids) BETWEEN 1 AND 5GROUP BY  list_previous.idORDER BY  id DESC

Please see fiddle here. I won't recommend using this trick for big tables, but for small sets of data it is fine.

This is maybe not the simplest solution, but it does do the job and is an interesting variation and in my opinion transparent. I simulate the analytical functions that I know from Oracle.

As we do not assume the id to be consecutive the counting of the rows is simulated by increasing @rn each row. Next products table including the rownum is joint with itself and only the rows 2-6 are used to build the average.

select p2id, avg(deposit), group_concat(p1id order by p1id desc), group_concat(deposit order by p1id desc)  from ( select p2.id p2id, p1.rn p1rn, p1.deposit, p2.rn p2rn, p1.id p1id           from   (select p.*,@rn1:=@rn1+1 as rn from products p,(select @rn1 := 0) r) p1                , (select p.*,@rn2:=@rn2+1 as rn from products p,(select @rn2 := 0) r) p2 ) r  where p2rn-p1rn between 1 and 5  group by p2id  order by p2id desc  ;

Result:

+------+--------------+---------------------------------------+------------------------------------------+| p2id | avg(deposit) | group_concat(p1id order by p1id desc) | group_concat(deposit order by p1id desc) |+------+--------------+---------------------------------------+------------------------------------------+|   10 |      86.0000 | 9,8,7,6,5                             | 90,90,75,75,100                          ||    9 |      84.0000 | 8,7,6,5,4                             | 90,75,75,100,80                          ||    8 |      84.0000 | 7,6,5,4,3                             | 75,75,100,80,90                          ||    7 |      84.0000 | 6,5,4,3,2                             | 75,100,80,90,75                          ||    6 |      79.0000 | 5,4,3,2,1                             | 100,80,90,75,50                          ||    5 |      73.7500 | 4,3,2,1                               | 80,90,75,50                              ||    4 |      71.6667 | 3,2,1                                 | 90,75,50                                 ||    3 |      62.5000 | 2,1                                   | 75,50                                    ||    2 |      50.0000 | 1                                     | 50                                       |+------+--------------+---------------------------------------+------------------------------------------+

SQL Fiddle Demo: http://sqlfiddle.com/#!2/c13bc/129

I want to thank this answer on how to simulate analytical functions in mysql: MySQL get row position in ORDER BY