CODEWITHSUNDEEP
mysqlsql
David Zadražil
David Zadražil

Reputation: 97

Average value compared to previous month

I've worte SQL query for average duration from order date to sent date. It is for my e-commerce project. But I want compare it with previous month.

SELECT AVG(DATEDIFF(os.time, o.ordered)) AS average_value, 
       YEAR(os.time) AS groupYear, 
       MONTH(os.time) AS groupMonth 
FROM orders AS o 
       LEFT JOIN order_status_history os ON o.id = os.order_id 
WHERE  os.status_id = 2 
GROUP BY groupyear, groupmonth

I want in select average value from previous month too. I've try LEFT JOIN with ON -1 month. But it showed bad values, so it's not probably right way.

o.ordered and os.time are both Datetime.

Thanks everyone for help.

Upvotes: 2

Views: 160

Answers (2)

agold
agold

Reputation: 6276

My solution is to create a view for the subquery first:

CREATE VIEW MonthlyValue AS        
SELECT YEAR(os.time) AS groupYear, 
       MONTH(os.time) AS groupMonth, 
       os.status_id,
       AVG(DATEDIFF(os.time, o.ordered)) AS average_value
FROM orders AS o 
      LEFT JOIN order_status_history os ON o.id = os.order_id 
GROUP BY groupyear, groupmonth,os.status_id;

Then you can use the view to get information of the previous and current month:

SELECT tc.groupYear as year, tc.groupMonth as month, tp.groupYear prevYear, tp.groupMonth as prevMonth,
    mv.average_value, mvprev.average_value, mv.average_value - mvprev.average_value as difference
FROM MonthlyValue mv join MonthlyValue mvprev on mv.status_id=mvprev.status_id and
     (mv.groupYear=mvprev.groupYear and mvprev.groupMonth=mv.groupMonth-1 or 
            mv.groupMonth=1 and mvprev.groupMonth=12 and mvprev.groupYear=mv.groupYear-1)
WHERE mv.status_id = 2;

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

this is not tested. if it not work you can post some sampledata and create statements

SELECT AVG(DATEDIFF(os.time, o.ordered)) AS average_value, 
       YEAR(os.time) AS groupYear, 
       MONTH(os.time) AS groupMonth 
FROM orders AS o
LEFT JOIN order_status_history os ON o.id = os.order_id 
LEFT JOIN (
            SELECT AVG(DATEDIFF(os.time, o.ordered)) AS average_value, 
                   YEAR(os.time) AS groupYear, 
                   MONTH(os.time) AS groupMonth 
            FROM orders AS o
            LEFT JOIN order_status_history os ON o.id = os.order_id 
            WHERE  os.status_id = 2 
            GROUP BY DATE_FORMAT(os.`time`, '%Y%my')
          ) AS l ON l.groupYear = YEAR(SUBDATE(os.time, INTERVAL 1 MONTH)) AND l.groupMonth = MONTH(SUBDATE(os.time, INTERVAL 1 MONTH))
WHERE  os.status_id = 2 
GROUP BY DATE_FORMAT(os.`time`, '%Y%my');

Upvotes: 1

Related Questions