MySQL get percentage of year over year for each month

357 Views Asked by At

I'm trying to get in the same result the last 12 months and their values vs same months from the last year and the percentage difference.

ThisYearMonth | ThisYearValue | LastYearMonth | LastYearDifAsPercent
--------------|---------------|---------------|---------------------
Dec 2015 -----|800 -----------|Dec 2014 ------|50 ------------------
Nov 2015 -----|350 -----------|Nov 2014 ------|-23 -----------------

Each select works OK, but when combined, i get

[Err] 1242 - Subquery returns more than 1 row


SELECT
    DATE_FORMAT(`month`, '%b %Y') AS ThisYearMonth,
    no_customer_growth as ThisYearCustGrowth,
    DATE_FORMAT(DATE_SUB(`month`, INTERVAL 1 YEAR),'%b %Y') AS `LastYearMonth`,
    (
        SELECT
        ROUND((t1.no_customer_growth - t2.no_customer_growth) / t2.no_customer_growth * 100, 2)
        FROM cust_evolution AS t1
        INNER JOIN cust_evolution AS t2
        ON DATE_FORMAT(t1.`month`,'%b') = DATE_FORMAT(t2.`month`,'%b')
        AND DATE_FORMAT(t1.`month`,'%Y') = DATE_FORMAT(t2.`month`,'%Y') - 1
    ) AS `MoM % growth`
FROM cust_evolution

Thanks for your time and support!

0

There are 0 best solutions below