'add count from 2 different tables - SQL Server

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
sqlsql-server


Solution 1:[1]

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')

Solution 2:[2]

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

Solution 3:[3]

You need something like SQL INNER JOINing 2 Subqueries

Here is an example similar to your tables:

select * from (
    (
        select count(*) AS C1, Convert(date, StartDate) as StartDate  from Table1
        group by IdType, Convert(date, StartDate)
    ) A
    left join -- or inner
    (
        select count(*) AS C2, Convert(date, StartDate) as StartDate   from Table2
        group by IdType2, Convert(date, StartDate)
    ) B
    ON A.StartDate = B.StartDate
)

And here an exaple output:

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MatBailie
Solution 2 danlvr
Solution 3 Dani

Related Questions