Merge 2 MYSQL query into same table

Asked

Viewed 58 times

0

I have this consultation where it results the date, the count, and the sum of products of the entry

$result_entrada_mes = "SELECT DATE_FORMAT(data,'%m/%Y') as data, 
                        COUNT(quantidade) as qnt, 
                        SUM(quantidade) as soma 
                        FROM usuarios GROUP BY MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)";

And I have this query where generates the product output data, where the code is almost the same, possessing only one condition where the results do not appear where the output date is null.

$result_saida_mes = "SELECT DATE_FORMAT(data_saida,'%m/%Y') as data1, 
                      COUNT(quantidade) as qnt1, 
                        SUM(quantidade) as soma1 
                       FROM usuarios 
                      WHERE data_saida IS NOT NULL 
                      GROUP BY MONTH(data_saida), YEAR(data_saida) ORDER BY YEAR(data_saida), MONTH(data_saida)";

I would like to unite these two queries so I can insert the codes in a chart, generating two lines, one of input chart, and one of output, as I can join these two queries to display in a while only?

mysql

  • have you thought about using UNION? the fields are the same, should work well: https://dev.mysql.com/doc/refman/8.0/en/union.html

  • If you want the input and output data on the same line you can use a self FULL OUTER JOIN on (input.data = output.data_output), where input and aids are different roles of the user table.

1 answer

0

UNION combines the execution result of the two queries.

$result = "SELECT DATE_FORMAT(data,'%m/%Y') as data, 
                 COUNT(quantidade) as qnt, 
                 SUM(quantidade) as soma 
            FROM usuarios GROUP BY MONTH(data), YEAR(data) ORDER BY YEAR(data), MONTH(data)
          UNION
          SELECT DATE_FORMAT(data_saida,'%m/%Y') as data, 
                 COUNT(quantidade) as qnt, 
                 SUM(quantidade) as soma 
            FROM usuarios 
           WHERE data_saida IS NOT NULL 
           GROUP BY MONTH(data_saida), YEAR(data_saida) ORDER BY YEAR(data_saida), MONTH(data_saida)";

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.