0

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
aaossa
  • 3,763
  • 2
  • 21
  • 34
oliveira0501
  • 3
  • 1
  • Write your queries as two CTEs or derived tables, then select from each and *join* them. – Stu Mar 03 '22 at 11:50
  • 2
    You would be *far*better off using `DATEPART` or `DATENAME` here. `FORMAT` is known to perform poorly. – Thom A Mar 03 '22 at 11:54
  • Should your counts for any particular month include multiple years? Or is your test data limited to a single year so you do not see this potential flaw? – SMor Mar 03 '22 at 13:51

4 Answers4

2

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')
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • you missed out the quantity in the UNIONed selects, don't know if that was just to keep the anwer brief – Aaron Reese Mar 03 '22 at 12:08
  • @AaronReese I left it out because it's not required. All the aggregation (count) is done in the outer query. Adding `COUNT(*)` to the inner queries would require significant duplication of code (the aggregate, group by clauses, and potentially the date processing too) for no benefit. This code is shorter, DRYer, easier to maintain, and fully functional. – MatBailie Mar 03 '22 at 12:15
  • `EOMONTH(DateTimeEmission)` probably significantly more efficient than `FORMAT` – Charlieface Mar 03 '22 at 12:47
1
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.

Aaron Reese
  • 544
  • 6
  • 18
0

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

danlvr
  • 1
  • 2
0

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

Dani
  • 1,825
  • 2
  • 15
  • 29
  • This would ***need*** to be a FULL OUTER JOIN, to cater for dates in the second table but missing from the first. Just like the first answer submitted to this question by @aaronreese – MatBailie Mar 03 '22 at 12:19
  • left join -- or inner or whathever you want... – Dani Mar 03 '22 at 12:20
  • ***NEED*** not ***want***, as written the answer is wrong. – MatBailie Mar 03 '22 at 12:21
  • Something like, example similar... Is just an example for join two subquerys – Dani Mar 03 '22 at 12:56
  • yes Dani, but Inner Joint and Left join won't work in all cases. you need FULL OUTER JOIN to guarantee not to drop any records. INNER will only give you records where both tables match LEFT will only give you records in the 2nd table if there is a match in the first table only FULL OUTER will give you ALL records from BOTH tables and align then if there is a match. – Aaron Reese Mar 03 '22 at 14:37