How to merge these 2 SELECT into a single query

Question

Good morning, I’m redoing the question because the last one I deleted was a little confused.

I have these two select and would like to join them to return the result to a comparison chart.

This SELECT query and returns the number of maintenance you had per month

SELECT
DATE(MAN.relManu_data_registro) AS DATA,
count(MAN.relManu_id) AS TOTAL_MAN

FROM tb_relatorio_manutencao AS MAN


WHERE
MAN.relManu_data_registro >= DATE(date_sub(now(), interval 1 MONTH))

GROUP BY DAY(MAN.relManu_data_registro) ORDER BY MAN.relManu_data_registro ASC

This SELECT query and returns the number of calls you had per month

  SELECT
  DATE(LIG.ligacoes_data_registro) AS DATA,
  count(LIG.ligacoes_id) AS TOTAL_LIG

  FROM tb_relatorio_ligacoes AS LIG


   WHERE
   LIG.ligacoes_data_registro >= DATE(date_sub(now(), interval 1 YEAR)) AND
   LIG.ligacoes_assunto >= suporte

   GROUP BY MONTH(LIG.ligacoes_data_registro) 

   ORDER BY LIG.ligacoes_data_registro ASC

Then, in the first select I select the maintenance and count how many had in the month, in the second I consult another table and get how many calls to the support had per month.

I wanted to join these queries to stay on the same line the information of the DATE MONTH, CONNECTION COUNT, SUPPORT COUNT to compare on a graph.

Answer 1

Another solution would be to create seasons and then make the third select them

SELECT
DATE(MAN.relManu_data_registro) AS DATA,
count(MAN.relManu_id) AS TOTAL_MAN

INTO #TEMP1
FROM tb_relatorio_manutencao AS MAN


WHERE
MAN.relManu_data_registro >= DATE(date_sub(now(), interval 1 MONTH))

GROUP BY DAY(MAN.relManu_data_registro) ORDER BY MAN.relManu_data_registro ASC


SELECT
DATE(LIG.ligacoes_data_registro) AS DATA,
count(LIG.ligacoes_id) AS TOTAL_LIG

INTO #TEMP2
FROM tb_relatorio_ligacoes AS LIG


WHERE
LIG.ligacoes_data_registro >= DATE(date_sub(now(), interval 1 YEAR)) AND
LIG.ligacoes_assunto >= suporte

GROUP BY MONTH(LIG.ligacoes_data_registro) 

ORDER BY LIG.ligacoes_data_registro ASC

select * from #TEMP1 CROSS JOIN #TEMP2

Answer 2

Speak @Douglas, a possible solution would be by subquery:

SELECT 
    DATE(BASE.relManu_data_registro) AS DATA,

    (SELECT 
         count(MAN.relManu_id) 
     FROM 
         tb_relatorio_manutencao AS MAN
     WHERE 
         MAN.relManu_data_registro = BASE.relManu_data_registro
     AND 
         MAN.relManu_data_registro >= DATE(date_sub(now(), interval 1 MONTH))
     GROUP BY 
         MAN.relManu_data_registro)
     AS TOTAL_MAN,

    (SELECT 
         count(LIG.ligacoes_id) 
     FROM 
         tb_relatorio_ligacoes AS LIG
     WHERE 
         DATE(LIG.ligacoes_data_registro) = DATE(BASE.relManu_data_registro) 
     AND 
         LIG.ligacoes_data_registro >= DATE(date_sub(now(), interval 1 YEAR)) 
     AND 
         LIG.ligacoes_assunto >= suporte
     GROUP BY 
         LIG.ligacoes_data_registro) 
AS TOTAL_LIG
FROM 
    tb_relatorio_manutencao AS BASE
WHERE 
    BASE.relManu_data_registro >= DATE(date_sub(now(), interval 1 MONTH))