Sergio Suarez Sergio Suarez - 8 days ago 4
MySQL Question

Join two querys in one mysql

I have these two tables.

Table

INCOMES



ID | VALIDATED | REVENUE | DATE


1         123                 12            2017-03-01

2         99                  44             2017-03-01

3         66                  44             2017-04-01

4         66                  44             2017-04-01

5         66                  44             2017-05-01


Table
COSTES



ID | COSTS | DATE

1      123        2017-04-01

2      99          2017-04-01

3      77          2017-05-01

4      33          2017-05-01

5      66          2017-06-01

I need to get SUM(VALIDATED) [sum of a full month, for example 2017-03 is 123 + 99], SUM(REVENUE)[sum of a full month, for example 2017-03 is 12 + 44], SUM(COSTS)[sum of a full month, for example 2017-03 is 0 + 0, beucase there is no costs in that month] and DATE VALUES orderer by DATE.
The result should be something like that:

RESULT QUERY

VALIDATED | REVENUE | COSTS | DATE

222                      56                 0         2017-03

132                     88                 22        2017-04

66                      44                   22        2017-05

0                         0                    66         2017-06


i have already that QUERY separated


SELECT
SUM(validated) AS 'validated',
SUM(revenue) AS 'revenue',
DATE_FORMAT(Date, '%Y-%m') AS Date
FROM incomes WHERE date LIKE '%2017%'
GROUP BY(DATE_FORMAT(Date, '%Y-%m'));





SELECT
SUM(validacion) AS 'costes',
DATE_FORMAT(Date, '%Y-%m') AS Date
FROM costes WHERE date LIKE '%2017%'
GROUP BY(DATE_FORMAT(Date, '%Y-%m'));


please help will be appreciated.

Answer

You need to use the UNION statment for this goal.

The SELECT call need to contain the same columns names. So the SGBD crates a temp table (that I called "union_tb") so you can play with it as you want.

Take a Look:

SELECT SUM(union_tb.validated) AS validated
, SUM(union_tb.revenue) AS revenue
, SUM(union_tb.costes) AS costes
, union_tb.date

FROM (

        SELECT SUM(validated) AS 'validated'
        , 0 AS 'costes'
        ,  SUM(revenue) AS 'revenue'
        , DATE_FORMAT(DATE, '%Y-%m') AS 'date'

            FROM incomes
            WHERE YEAR(DATE) = 2017
            GROUP BY(DATE_FORMAT(DATE, '%Y-%m'))

    UNION

        SELECT 0 AS 'validated'
        ,  SUM(validacion) AS 'costes'
        , 0 AS 'revenue'
        ,  DATE_FORMAT(DATE, '%Y-%m') AS 'date'

        FROM costes
        WHERE YEAR(DATE) = 2017
        GROUP BY(DATE_FORMAT(DATE, '%Y-%m'))
) AS union_tb 

WHERE 1 
GROUP BY union_tb.date ;