CODEWITHSUNDEEP
mysqlsql
tman
tman

Reputation: 425

MYSQL Subquery average count from last X days

Assume I have the following type of table, named item_sales:

Item, Sales Date, Num_Sold
--------------------------
item1, 2017-01-02, 500
item1, 2017-01-03, 350
item1, 2017-01-04, 450
item2, 2017-01-02, 750
item2, 2017-01-03, 1100
item2, 2017-01-04, 1000
item3, 2017-01-02, 200
item3, 2017-01-03, 100
item3, 2017-01-04, 100

And so on. I know how to return the average sales volume by item.

select item, avg_sold 
 from 
 (
   select item, avg(num_sold) as avg_sold 
   from item_sales 
   group by item
 ) as avg_table 
where avg_sold > 200;

and this returns

item   avg_sold
------|-------
item1 | 433.00
item2 | 950.00

Now what I'd like to be able to do is return the same average but look back over the past X days, say over the past 30 or 60 days. I'm not sure how to do that.

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Your query doesn't need a subquery, only a having clause:

select item, avg(num_sold) as avg_sold 
from item_sales 
group by item
having avg_sold > 200;

Then, you just need to add a where:

select item, avg(num_sold) as avg_sold 
from item_sales 
where sale_date >= date_sub(curdate(), interval 30 day)
group by item
having avg_sold > 200;

Upvotes: 4

Related Questions