Unknown column in mysql subquery

Question

I am trying to get the avg of an item so I am using a subquery.

Update: I should have been clearer initially, but i want the avg to be for the last 5 items only

First I started with

SELECT 
y.id
FROM (
    SELECT *
        FROM (
                SELECT *
                FROM products
                WHERE itemid=1
        ) x  
    ORDER BY id DESC
    LIMIT 15 
) y;

Which runs but is fairly useless as it just shows me the ids.

I then added in the below

SELECT
y.id,
(SELECT AVG(deposit) FROM (SELECT deposit FROM products WHERE id < y.id ORDER BY id DESC LIMIT 5)z) AVGDEPOSIT
FROM (
    SELECT *
        FROM (
                SELECT *
                FROM products
                WHERE itemid=1
        ) x  
    ORDER BY id DESC
    LIMIT 15 
) y;

When I do this I get the error Unknown column 'y.id' in 'where clause', upon further reading here I believe this is because when the queries go down to the next level they need to be joined?

So I tried the below ** removed un needed suquery

SELECT
y.id,
(SELECT AVG(deposit) FROM (
    SELECT deposit 
    FROM products
    INNER JOIN y as yy ON products.id = yy.id       
    WHERE id < yy.id 
    ORDER BY id DESC 
    LIMIT 5)z
    ) AVGDEPOSIT
FROM (
    SELECT *
    FROM products
    WHERE itemid=1
    ORDER BY id DESC
    LIMIT 15 
) y;

But I get Table 'test.y' doesn't exist. Am I on the right track here? What do I need to change to get what I am after here?

The example can be found here in sqlfiddle.

CREATE TABLE products
    (`id` int, `itemid` int, `deposit` int);

    INSERT INTO products
    (`id`, `itemid`, `deposit`)
VALUES
(1, 1, 50),
(2, 1, 75),
(3, 1, 90),
(4, 1, 80),
(5, 1, 100),
(6, 1, 75),
(7, 1, 75),
(8, 1, 90),
(9, 1, 90),
(10, 1, 100);

Given my data in this example, my expected result is below, where there is a column next to each ID that has the avg of the previous 5 deposits.

id | AVGDEPOSIT
10 | 86 (deposit value of (id9+id8+id7+id6+id5)/5) to get the AVG
 9 | 84
 8 | 84
 7 | 84
 6 | 79
 5 | 73.75

@SajunaFernando I have edited the question to show this and added in the sqlfiddle. — ak85, Oct 13 '13 at 01:32
Could you please add in your question, what is the expected result you would want with the data you inserted in the fiddle? — mavrosxristoforos, Oct 15 '13 at 05:22
Please describe in English exactly what you're trying to do. — Bohemian, Oct 15 '13 at 06:14

Roman Pekar · Accepted Answer · 2013-10-15T11:23:16.913

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 avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 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 avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 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 avgdeposit
from 
(
    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 brings back the results I am after thanks. I will continue to do some testing and let you know if I find anything that needs to be changed here but so far it looks great! — ak85, Oct 15 '13 at 20:02

fthiella · Answer 2 · 2013-10-17T10:37:12.757

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_ids
FROM
  products p1 LEFT JOIN products p2
  ON p1.itemid=p2.itemid AND p1.id>p2.id
GROUP BY
  p1.id, p1.itemid
ORDER 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 5
GROUP BY
  list_previous.id
ORDER 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.

score 3 · Answer 3 · edited May 23 '17 at 12:05

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

Bohemian · Answer 4 · 2013-10-15T11:44:43.100

2

It looks like you just want:

SELECT
  id,
  (SELECT AVG(deposit)
   FROM (
        SELECT deposit
        FROM products
        ORDER BY id DESC
        LIMIT 5) last5
   ) avgdeposit
FROM products

The inner query gets the last 5 rows added to product, the query that wraps that gets the average for their deposits.

edited Oct 15 '13 at 11:44

answered Oct 15 '13 at 06:17

Bohemian

412,405
93
575
722

I have provided more info, Your answer is on the right track I want that AVG to be of the last 5 results though. – ak85 Oct 15 '13 at 11:13
thanks I am looking for the avg to change for each item like it does in the top answer so far. – ak85 Oct 15 '13 at 20:03
Ahhh you want the average deposit for the *preceding* 5 products (based on id) with itemid of 1? And although your example data doesn't show it, itemid may be other than 1? And if its other than 1, does it count as "latest 5" but not towards the total, or should I find 5 rows with itemid 1 no matter how far back? – Bohemian Oct 15 '13 at 20:12
Yes sorry for the confusion, where itemid = the itemid of the current id (in my fiddle this is always 1 but probably best not to hardcode 1 in there) – ak85 Oct 15 '13 at 20:40
You should add in more rows in between the other rows because at the moment you could leave out the itemid condition and still get the right answer. It is especially pertinent if rows with different itemids are interleaved in a series of sequential ids. Could you please adjust your sample data and sqlfiddle to be tougher – Bohemian Oct 15 '13 at 23:36

bean5 · Answer 5 · 2013-10-14T06:11:19.673

0

I'm going to simplify your query a bit so I can explain it.

SELECT
y.id,
(
    SELECT AVG(deposit) FROM 
    (
        SELECT deposit 
        FROM products
        LIMIT 5
    ) z
) AVGDEPOSIT
FROM 
(
    SELECT *
    FROM 
    (
        SELECT *
        FROM products
    ) x  
    LIMIT 15 
) y;

My guess would be that you just need to insert some AS keywords in there. I'm sure someone else will come up with something more elegant, but for now you can try it out.

SELECT
y.id,
(
    SELECT AVG(deposit) FROM 
    (
        SELECT deposit 
        FROM products
        LIMIT 5
    ) z
) AS AVGDEPOSIT
FROM 
(
    SELECT *
    FROM 
    (
        SELECT *
        FROM products
    ) AS x
    LIMIT 15 
) y;

edited Oct 14 '13 at 06:11

answered Oct 13 '13 at 00:09

bean5

306
2
9

What's the purpose of nesting `SELECT * FROM products`? – zerkms Oct 13 '13 at 00:11
I only simplified to make the syntax for the `AS` keywords easier to see. He @ak85 would probably want to leave the original select statement. – bean5 Oct 14 '13 at 06:10

score 0 · Answer 6 · answered Oct 17 '13 at 02:42

Here's one way to do it in MySQL:

SELECT p.id
     , ( SELECT AVG(deposit)
           FROM ( SELECT @rownum:=@rownum+1 rn, deposit, id
                    FROM ( SELECT @rownum:=0 ) r
                       , products
                   ORDER BY id ) t
          WHERE rn BETWEEN p.rn-5 AND p.rn-1 ) avgdeposit
  FROM ( SELECT @rownum1:=@rownum1+1 rn, id
           FROM ( SELECT @rownum1:=0 ) r
              , products
          ORDER BY id ) p
 WHERE p.rn >= 5
 ORDER BY p.rn DESC;

It's a shame MySQL doesn't support the WITH clause or windowing functions. Having both would greatly simplify the query to the following:

WITH tbl AS (
    SELECT id, deposit, ROW_NUMBER() OVER(ORDER BY id) rn
      FROM products
)
SELECT id
     , ( SELECT AVG(deposit)
           FROM tbl
          WHERE rn BETWEEN t.rn-5 AND t.rn-1 ) 
  FROM tbl t
 WHERE rn >= 5
 ORDER BY rn DESC;

The latter query runs fine in Postgres.

score 0 · Answer 7 · answered Oct 17 '13 at 11:16

2 possible solutions here

Firstly using user variables to add a sequence number. Do this twice, and join the second set to the first where the sequence number is between the id - 1 and the id - 5. Then just use AVG. No correlated sub queries.

SELECT Sub3.id, Sub3.itemid, Sub3.deposit, AVG(Sub4.deposit)
FROM
(
    SELECT Sub1.id, Sub1.itemid, Sub1.deposit, @Seq:=@Seq+1 AS Sequence
    FROM
    (
        SELECT id, itemid, deposit
        FROM products
        ORDER BY id DESC
    ) Sub1
    CROSS JOIN
    (
        SELECT @Seq:=0
    ) Sub2
) Sub3
LEFT OUTER JOIN
(
    SELECT Sub1.id, Sub1.itemid, Sub1.deposit, @Seq1:=@Seq1+1 AS Sequence
    FROM
    (
        SELECT id, itemid, deposit
        FROM products
        ORDER BY id DESC
    ) Sub1
    CROSS JOIN
    (
        SELECT @Seq1:=0
    ) Sub2
) Sub4
ON Sub4.Sequence BETWEEN Sub3.Sequence + 1 AND Sub3.Sequence + 5
GROUP BY Sub3.id, Sub3.itemid, Sub3.deposit
ORDER BY Sub3.id DESC

Second one is cruder, and uses a correlated sub query (which is likely to perform poorly as the amount of data increases). Does a normal select but for the last column it has a sub query that refers to the id in the main select.

SELECT id, itemid, deposit, (SELECT AVG(P2.deposit) FROM products P2 WHERE P2.id BETWEEN P1.id - 5 AND p1.id - 1 ORDER BY id DESC LIMIT 5)
FROM products P1
ORDER BY id DESC

score 0 · Answer 8 · answered Oct 19 '13 at 17:23

Is this what you are after?

SELECT m.id
     , AVG(d.deposit) 
  FROM products m
     , products d
 WHERE d.id < m.id
   AND d.id >= m.id - 5
 GROUP BY m.id
 ORDER BY m.id DESC
;

But can't be that simple. Firstly, the table cannot just contain one itemid (hence your WHERE clause); Second, the id cannot be sequential/without gaps within an itemid. Thirdly, you probably want to produce something that runs across itemid and not one itemid at a time. So here it is.

SELECT itemid
     , m_id as id
     , AVG(d.deposit) as deposit
  FROM (
        SELECT itemid
             , m_id
             , d_id
             , d.deposit
             , @seq := (CASE WHEN m_id = d_id THEN 0 ELSE @seq + 1 END) seq
          FROM (
                SELECT m.itemid
                     , m.id m_id
                     , d.id d_id
                     , d.deposit
                  FROM products m
                     , products d
                 WHERE m.itemid = d.itemid
                   AND d.id <= m.id
                 ORDER BY m.id DESC
                     , d.id DESC) d
             , (SELECT @seq := 0) s
        ) d
 WHERE seq BETWEEN 1 AND 5
 GROUP BY itemid
     , m_id
 ORDER BY itemid 
     , m_id DESC
;

Unknown column in mysql subquery

8 Answers8

Linked