add count from 2 different tables - SQL Server-CodePudding

I have 2 same queries, but from different tables. I need to add up the number of months. For example: Jan tab1 Jan tab2 = Jan. I believe I should do this on a temp table, but I can't move forward.

Query1


SELECT
FORMAT(DateTimeEmission, 'MMM','pt-BR') as Mês,
COUNT (*) as Quantidade
FROM
[dbo].[QuotationOne]
GROUP BY
FORMAT(DateTimeEmission, 'MMM', 'pt-BR')

Result from query1:

ago 551
dez 688
fev 430
jan 468
nov 603
out 557
set 626

Query2

SELECT
FORMAT(DateTimeEmission, 'MMM','pt-BR') as Mês,
COUNT (*) as Quantidade
FROM
[dbo].[QuotationTwo]
GROUP BY
FORMAT(DateTimeEmission, 'MMM', 'pt-BR')

Result from query2:

ago 15
dez 19
fev 21
jan 32
nov 26
out 32
set 16

I need the query to be:

ago 551   15
dez 688   19
fev 430   21
jan 468   32
nov 603   26
out 557  32
set 626   16

CodePudding user response：

Union the two tables together in a sub query, then run your aggregation against the result.

SELECT
  FORMAT(DateTimeEmission, 'MMM','pt-BR') as Mês,
  COUNT(*) as Quantidade
FROM
(
  SELECT DateTimeEmission FROM [dbo].[QuotationOne]

  UNION ALL

  SELECT DateTimeEmission FROM [dbo].[QuotationTwo]
)
  AS Quotation
GROUP BY
  FORMAT(DateTimeEmission, 'MMM', 'pt-BR')

CodePudding user response：

SELECT 
    COALESCE(Q1.Mes,Q2.Mes) AS [Mes]
    ,COLALESCE(Q1.Quantidade,0)   COALESCE(Q2.Quantidade,0) AS [Quantidade]
FROM
    (...Query1) AS Q1
  FULL OUTER JOIN
    (...Query2) AS Q2 ON Q2.Mes = Q1.Mes

Replace the ...Query1 with your 1st query and ...Query2 with your second query. The full outer join will return records from both queries and align them if the months match The COALESCE() makes sure that you don't get NULL values. The first one will take the month from Q1 or Q2 and the ones in the addition will return 0 if the particular query does not have a row returned.

CodePudding user response：

You can use SQL UNION ALL to combine the result sets of 2 or more SELECT statements.

CodePudding user response：

You need something like