How to join these 2 SELECTs in the same query

Navigation
0

Good morning, I'm redoing the question because the last one I deleted because it was a bit confusing.

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

This SELECT query and returns me the number of maintenances 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 me the number of connections 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

So, in the first select I select the maintenances and count how many I 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 merge these queries to stay on the same line of DATE MONTH, CONNECT COUNT, SUPPORT COUNT to compare on a chart.

    
asked by anonymous 01.02.2018 / 15:23

2 answers

1

Another solution would be to create temporary and then make the third select urnindo 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
    
03.02.2018 / 22:41
0

Speak @Douglas, a possible solution would be for 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))
    
03.02.2018 / 22:30
Bootstrap: sort by column instead of row Consume WebService in delphi