Incorrect sum values from MySQL select query-CodePudding

I have two MySQL Tables table1: INCOME and table2: OUTCOME And what i have tried so far is wrong because my select query output is incorrect.

This query is output me wrong data (huge amount of sums income and outcome)

Table income:

| id | date (datetime) | total_amount (int) |

Table outcome:

| id | date (datetime) | total_amount (int) |

SELECT
    MONTH(i.date) AS month,  
    SUM(i.total_amount) AS income,
    SUM(o.total_amount) AS outcome
FROM income i 
    INNER JOIN outcome o
WHERE YEAR(i.date) = YEAR(CURRENT_DATE)
GROUP BY MONTH(i.date)

CodePudding user response：

You can use UNION ALL to query them seperately in a subquery and GROUP out side

SELECT 
  MONTH(d.date) AS month,  
  SUM(IF(d.type=1,d.total_amount,0)) AS income,
  SUM(IF(d.type=-1,d.total_amount,0)) AS outcome
FROM
(
    SELECT total_amount,date,1 as type
    FROM income
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
    
    UNION ALL
    
    SELECT total_amount,date,-1 as type
    FROM outcome
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
) as d
GROUP BY MONTH(d.date)