w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL month over month totals compared to previous month

Think the problem is you are getting rows counted multiple times on the join. Ie, you have a sum of all the amounts for a month, but each row from that month is being joined with all rows for the previous month.

Solution using a couple of subselect:-

SELECT ThisMonth.MONTH, ThisMonth.YEAR, ThisMonth.TOTAL,
PrevMonth.MONTH AS PREVIOUS_MONTH, PrevMonth.YEAR AS PREVIOUS_YEAR,
PrevMonth.TOTAL AS PREVIOUS_TOTAL
FROM
(
    SELECT MONTH(start_date) AS `month`,
            YEAR(start_date) AS `year`,
            EXTRACT(YEAR_MONTH FROM start_date) AS YearMonth,
            SUM(amount) AS total
    FROM trackings
    GROUP BY `month`, `year`, YearMonth
) ThisMonth
LEFT OUTER JOIN
(
    SELECT MONTH(start_date) AS `month`,
            YEAR(start_date) AS `year`,
            EXTRACT(YEAR_MONTH FROM DATE_ADD(start_date, INTERVAL 1 MONTH))
AS YearMonth,
            SUM(amount) AS total
    FROM trackings
    GROUP BY `month`, `year`, YearMonth
) PrevMonth
ON ThisMonth.YearMonth = PrevMonth.YearMonth




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.