Sergio Suarez Sergio Suarez - 4 months ago 13
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 Source

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 ;