select last 2 row created_at and average of field mysql

user_14688049 0
Viewed 40

my table1 :

id name

11   a
12   b
13   c
14   d

my table2 :

id table1_id  price   created_at

1   11         10    2020-11-19
2   11         12    2020-11-20
3   11         14    2020-11-21
4   11         16    2020-11-22
2   12         10    2020-11-16
3   12         12    2020-11-17
4   12         14    2020-11-18
2   13         16    2020-11-19
3   13         18    2020-11-20
4   13         20    2020-11-21
2   13         22    2020-11-22
2   14         10    2020-11-17
3   14         12    2020-11-18
4   14         14    2020-11-19
2   14         16    2020-11-20
3   14         18    2020-11-21
4   14         20    2020-11-22

i want select average 2 last created by tabel 1 id

for example table_id = 11 => avg(16+14) = 15

result :

table1_id  avg
11          15
12          13
13          21
14          19

I try this :

SELECT
subq.table1_id,avg( subq.price ) 
FROM
( SELECT price,table1_id FROM table2 WHERE table1_id = 11 ORDER BY created_at DESC LIMIT 2 ) AS subq
GROUP BY subq.table1_id

this only return one row becuse use => WHERE table1_id = 11

but i want all of avg price table_id 11,12,13,14 in one Table

mysql
2 Answers

You can use the 'in' operator like:

WHERE table1_id in (11,12,13,14)

so it will select if the id is one of those, a uglier approach would be to use the 'or' operator

WHERE table1_id = 11 or table1_id = 12 or table1_id = 13 or table1_id = 14
user_136510370

For MySql 8.0+ you can do it with ROW_NUMBER() window function:

SELECT table1_id, AVG(price) avg_price
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY table1_id ORDER BY created_at DESC) rn
  FROM table2
) t
WHERE rn <= 2
GROUP BY table1_id

See the demo.

user_104988280
Related