Calculate Growth rate I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle):
Schema:
CREATE TABLE `profit_log` (
  `market_id` smallint(6) NOT NULL,
  `total_purchase` bigint(20) NOT NULL,
  `total_profit` bigint(20) NOT NULL,
  `company_profit` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `tid` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Query:
SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`) * 100 as growth,
    ((d.`total_purchase` - e.`total_purchase`)/ e.`total_purchase`) * 100 as growth_week,
    ((f.`total_purchase` - g.`total_purchase`)/ g.`total_purchase`) * 100 as growth_month,
    ((d.`total_profit` - e.`total_profit`)/ e.`total_profit`) * 100 as growth_week_profit,
    ((f.`total_profit` - g.`total_profit`)/ g.`total_profit`) * 100 as growth_month_profit
FROM `profit_log` as a
JOIN `profit_log` as b on a.`market_id` = b.`market_id`
    AND b.`date` = '2016-05-05'
JOIN `profit_log` as c on a.`market_id` = c.`market_id`
    AND c.`date` = '2016-05-01'
JOIN `profit_log` as d on a.`market_id` = d.`market_id`
    AND d.`date` &gt;= ('2016-05-05' - INTERVAL 7 DAY) AND d.`date` &lt; ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as e on a.`market_id` = e.`market_id`
    AND e.`date` &gt;= ('2016-05-05' - INTERVAL 14 DAY) AND e.`date` &lt; ('2016-05-05' - INTERVAL 7 DAY)
JOIN `profit_log` as f on a.`market_id` = f.`market_id`
    AND f.`date` &gt;= ('2016-05-05' - INTERVAL 1 MONTH) and f.`date` &lt; ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as g on a.`market_id` = g.`market_id`
    AND g.`date` &gt;= ('2016-05-05' - INTERVAL 2 MONTH) AND g.`date` &lt; ('2016-05-05' - INTERVAL 1 MONTH)
WHERE a.`date` &gt;= '2016-05-01'
AND a.`date` &lt; ('2016-05-05' + INTERVAL 1 DAY)
AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

The Strange thing is after all this JOINs, SUMs (i.e SUM(a.total_purchase)) return unbelievably large numbers, it seems that values are accumulated over all this joins.
Can you please explain why is that happening (maybe i don't understand JOIN behavior), and help me to rewrite this query the proper way (6 self joins doesn't look right).
Thanks.
Explanation

First all the JOINing is done.  This inflates the number of rows.  Then you do a GROUP BY and SUM.  Hence, the "unbelievably large numbers".

Solution 1

A solution is usually to do the GROUP BY and SUM over just one table.  If that can't be done, then find the ids for that one table, dedup them (via DISTINCT or GROUP BY), then do the grouping and summing.

Give this a try...


Rip out all mention of tables other than a.  See if the grouping and summing works correctly.
Make that a subquery and put it as a 'table' in with the rest of the tables:

SELECT ...
    FROM ( SELECT ... SUM... FROM profit_log WHERE ... GROUP BY ... ) AS a
    JOIN b ... -- as you have the rest


Solution 2

Another approach is to use subqueries for the other stuff...

SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ( SELECT ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`)
        FROM `profit_log` as b on a.`market_id` = b.`market_id`
    ) * 100 as growth,
    ...
FROM `profit_log` as a
WHERE a.`date` &gt;= '2016-05-01'
  AND a.`date` &lt; ('2016-05-05' + INTERVAL 1 DAY)
  AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`


Need indexes

InnoDB tables really need a PRIMARY KEY.

Also you need this; it should increase performance significantly:  INDEX(market_id, date)