MySQL: the aggregated outcome is incorrect but not sure why? -

so trying find out customer largest sales change month month (in case, june july).

here's mockup data created sake of practice:

mysql> select * sales1; +------------+------------+-----------------+ | customerid | mydate     | purchase_amount | +------------+------------+-----------------+ |         10 | 1996-08-02 |         2540.78 | |         20 | 1999-01-30 |         1800.54 | |         30 | 1995-07-14 |          460.33 | |         10 | 1998-06-29 |            2400 | |         50 | 1998-02-03 |          600.28 | |         60 | 1998-03-02 |             720 | |         10 | 1998-07-06 |             150 | +------------+------------+-----------------+ mysql> select * sales2; +------------+------------+-----------------+ | customerid | mydate     | purchase_amount | +------------+------------+-----------------+ |         10 | 1996-06-02 |          540.78 | |         20 | 1999-09-30 |          800.54 | |         30 | 1995-07-14 |           60.33 | |         40 | 1998-01-29 |             400 | |         10 | 1998-07-03 |         2600.28 | |         60 | 1998-03-02 |            1720 | |         70 | 1998-05-04 |            4150 | +------------+------------+-----------------+ 

according above 2 tables, answer should customer customerid 10 , increase of sales 350.28 june july in year 1998.

below code achieve goal; created 2 view, 1 consisting of sum of june sales each customer each year, , 1 consisting of sum of july sales each customer each year, , subtract june sales july sales:

create view sum6 ( select customerid,  year(mydate) year,  month(mydate) month, sum(purchase_amount) amount sales1 group customerid, year, month having month = 6 )  union ( select customerid, year(mydate) year,  month(mydate) month, sum(purchase_amount) amount sales2 group customerid, year, month having month = 6)  ;  create view sum7 ( select customerid,  year(mydate) year,  month(mydate) month, sum(purchase_amount) amount sales1 group customerid, year, month having month = 7 )  union ( select customerid, year(mydate) year,  month(mydate) month, sum(purchase_amount) amount sales2 group customerid, year, month having month = 7)  ;  select customerid, year, (sum(sum7.amount)-sum(sum6.amount)) diff sum6 join sum7 using(customerid, year) group customerid, year ; 

however, output is:

+------------+------+--------------------+ | customerid | year | diff               | +------------+------+--------------------+ |         10 | 1998 | -2049.719970703125 | +------------+------+--------------------+ 

while yes, customerid , year values correct, the difference amount not.

i checked individually whether sums sum6 , sum7 calculated correctly customerid , year:

mysql> select customerid, year, sum(amount)     -> sum7     -> group customerid, year     -> ; +------------+------+-------------------+ | customerid | year | sum(amount)       | +------------+------+-------------------+ |         10 | 1998 | 2750.280029296875 | |         30 | 1995 | 520.6599884033203 | +------------+------+-------------------+ mysql> select customerid, year, sum(amount)     -> sum6     -> group customerid, year     -> ; +------------+------+------------------+ | customerid | year | sum(amount)      | +------------+------+------------------+ |         10 | 1996 | 540.780029296875 | |         10 | 1998 |             2400 | +------------+------+------------------+ 

and are, , know group correct.

so tried @ individual sum amounts:

mysql> select customerid, year, sum(sum7.amount), sum(sum6.amount)     -> sum6     -> join sum7     -> using(customerid, year)     -> group customerid, year     -> ; +------------+------+-------------------+------------------+ | customerid | year | sum(sum7.amount)  | sum(sum6.amount) | +------------+------+-------------------+------------------+ |         10 | 1998 | 2750.280029296875 |             4800 | +------------+------+-------------------+------------------+ 

so sum(sum7.amount) correct sum(sum6.amount) incorrect. how come can add correctly when pulled individually, , 1 of them summed incorrectly when combined? inconsistency driving me nuts...

your join statements incomplete.

you're joining sum6 sum7 loosely. use last case, join duplicating records somehow. (2400 * 2 = 4800)

when total them, you're somehow getting duplicate records 1 of views because of how joins set up. need check conditions

to narrow down, including rows , not taking math operations until can verify data. start following:

select * sum6 join sum7 using(customerid, year) 

and verify rows want pair pairing, , go there.