Calculating percentage growth month by month I have a basic database for a retail store (4 tables) and am trying to run a query that will calculate the percentage growth (or decline) month by month in revenue for the store. The ORDERS table holds the OrderPrice info for each month. 

I am struggling to find a query that will do this, does anyone know if one exists?

The DB is here: https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/0#&amp;togetherjs=009rG6HwUe

I have come up with this so far to SUM the revenue totals month by month:

SELECT * FROM (
SELECT Coalesce(MONTHNAME(ORDERS.OrderDate), 'Grand Total') AS OrderDate, 
SUM(ORDERS.OrderPrice) as SumOrderPrice  
FROM ORDERS
WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
GROUP BY MONTH(ORDERS.OrderDate) with ROLLUP)
AS T;
Well, you're close:

I tweaked your statement, and came up with the following. I made one notable assumption - that you want the output sorted by month number, with the rollup value last. If you ultimately need to show a list that runs October of one year through September of the next, you'll need to put in a little more work.

SELECT Coalesce(OrderDate, 'Grand Total') AS OrderDate
      ,SumOrderPrice
FROM (
      SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate
            ,SUM(ORDERS.OrderPrice) as SumOrderPrice
            ,MONTH(ORDERS.OrderDate) as MonthNum
      FROM ORDERS
      WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
      GROUP BY
            MONTHNAME(ORDERS.OrderDate)
           ,MONTH(ORDERS.OrderDate) with ROLLUP
     ) AS T
ORDER BY COALESCE(MonthNum,13);


Here's the modified DB-Fiddle link, including my version of the query.

What I changed, and why:


First, the USE GUITARWORLD at the top of your DB-Fiddle made the set-up code not run. In the future, please ensure that what you've posted in a DB-Fiddle does run successfully - having that just flat-out fail is a bad start :-).
Your GROUP BY isn't grouping by a value in the SELECT list. That isn't always a mistake, but that meant your COALESCE(MONTHNAME()) didn't work. I changed the GROUP BY to GROUP BY the MONTHNAME() results alone, and moved the COALESCE to the outer query, to ensure that the results from that weren't causing problems with the grouping. Since I wasn't using SELECT *, I also needed to explicitly return SumOrderPrice.


Initially, the results listed April, then March, then the Grand Total. I was reasonably certain you'd want to see the months in numerical order, instead of alphabetical (or indeterminate) order, so I added MONTH(Orders.OrderDate) as a new column in the inner query; added it to the GROUP BY (no change to the results, as a given month number will always have the matching given name), and sorted the results by MonthNum (adding the COALESCE to force the Grand Total to show up last.



For the next step, we'll use a variable:

SET @Last_Month_SumOrderPrice := 0;

SELECT OrderDate, SumOrderPrice, Monthly_Pct_Change
FROM (
      SELECT OrderDate
            ,SumOrderPrice
            ,CASE WHEN @Last_Month_SumOrderPrice = 0
               THEN NULL
               ELSE (SumOrderPrice - @Last_Month_SumOrderPrice) * 100.00 / @Last_Month_SumOrderPrice
             END as Monthly_Pct_Change
            ,@Last_Month_SumOrderPrice := SumOrderPrice
            ,MonthNum
      FROM (
            SELECT MONTHNAME(ORDERS.OrderDate) as OrderDate, 
                   SUM(ORDERS.OrderPrice) as SumOrderPrice,
                   MONTH(ORDERS.OrderDate) as MonthNum
            FROM ORDERS
            WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'
            GROUP BY MONTHNAME(ORDERS.OrderDate),
                     MONTH(ORDERS.OrderDate)
           ) AS T
      ORDER BY MonthNum) as SQ
ORDER BY MonthNum;


We take the basic query you already had, and add in a variable, @Last_Month_SumOrderPrice, to track the SumOrderPrice from the previous month as we go through the data. While the SELECT list data is being processed, the variable will retain its value from the last row.

This gives us access to the current and previous months' SumOrderPrice values. Calculating the percentage from there is trivial. I'm setting the value to NULL to avoid divide by zero errors.

We put another query outside the existing ones; otherwise, the value assigned to the variable would be output in every row.

Note that I've included an ORDER BY in the SQ sub-query, and in the outer query as well, both on MonthNum. The one in the sub-query guarantees the rows are processed in the correct order; the one in the outer query guarantees the final result is displayed in the correct order. It may be likely that the final result would be ordered correctly without the outer ORDER BY; however, the rule in SQL is order is only guaranteed when you specify an ORDER BY on your final result.

As a grand total would be misleading/irrelevant in this case, I've dropped that. If you have more data and want to see year-over-year percentage changes, I'd consider separate queries.

Here's the final DB-Fiddle link, showing both queries running.