CODEWITHSUNDEEP
sqlsql-server
oliveira0501
oliveira0501

Reputation: 3

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

Upvotes: 0

Views: 129

Answers (4)

Dani
Dani

Reputation: 2046

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

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

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

Upvotes: 2

danlvr
danlvr

Reputation: 11

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

Upvotes: 0

Aaron Reese
Aaron Reese

Reputation: 554

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.

Upvotes: 1

Related Questions