mysql - Calculate percentage on previous month (same year)

Question

How can i calculate and show the percentage of growth or loss, on the previous month (comparing the figure from the previous month, not previous year).

i.e. Jan is 500, so results will be 0%. Then as Feb is 150, the percentage loss is -70%, March will show -86.67 as their results were only 20 (compared to Febs 150)

+----------+------+------------+
| Month    | Sale | Difference |
+----------+------+------------|
| January  |  500 |            |
| February |  150 |     -70.00 |
| March    |   20 |     -86.67 |
| April    |  250 |    1150.00 |
| May      |  280 |      12.00 |
| June     |  400 |      42.86 |
| July     |  480 |      20.00 |
+----------+------+------------+

My script below produces: (I just need to add another percentage column

+----------+-------------------+
| MONTH    | SUM(SALES_AMOUNT) |
+----------+-------------------+
| January  |               500 |
| February |               150 |
| March    |                20 |
| April    |               250 |
| May      |               280 |
| June     |               400 |
| July     |               480 |
+----------+-------------------+


SELECT coalesce(date_format(DATE_PURCHASED, '%M')) AS MONTH,
SUM(SALE_PRICE)
FROM SALE
WHERE YEAR(DATE_PURCHASED) = 2017
GROUP BY month

Answer 1

You might left join the query to itself on the number of the month minus 1. Then you had the sum of the month an the last month in one row and could calculate the percentage. Similar to the following:

SELECT monthname(concat('1970-', lpad(A.MONTH, 2, '0'), '-01')) AS MONTH,
       A.SALE_PRICE,
       CASE
         WHEN A.SALE_PRICE IS NULL
           THEN NULL
         WHEN B.SALE_PRICE IS NULL
           THEN NULL
         WHEN A.SALE_PRICE = 0
           THEN NULL
         ELSE
           (B.SALE_PRICE / A.SALE_PRICE - 1) * 100
       END AS PERCENTAGE
       FROM (SELECT month(DATE_PURCHASED) AS MONTH,
                    sum(SALE_PRICE) AS SALE_PRICE,
                    FROM SALE
                    WHERE year(DATE_PURCHASED) = 2017
                    GROUP BY MONTH) A
            LEFT JOIN (SELECT month(DATE_PURCHASED) AS MONTH,
                              sum(SALE_PRICE) AS SALE_PRICE,
                              FROM SALE
                              WHERE year(DATE_PURCHASED) = 2017
                              GROUP BY MONTH) B
                      ON A.MONTH - 1 = B.MONTH
       ORDER BY A.MONTH;

Note A and B being your query, just modified so that the month is numeric as this is needed in the ON clause.