CODEWITHSUNDEEP
sqlsql-server
Selim Yildiz
Selim Yildiz

Reputation: 5370

How to sum of count from 2 queries in SQL

I have 2 tables called NotificationEvent and NotificationEventLog in SQL with the same structure.

I have 2 queries that select EventCount group by Month for each table:

First query for NotificationEvent:

SELECT MONTH(CreationDate) Month, COUNT(*) AS EventCount
FROM NotificationEvent
WHERE YEAR(CreationDate) = 2020
GROUP BY YEAR(CreationDate), MONTH(CreationDate)
ORDER BY YEAR(CreationDate), MONTH(CreationDate)

Output:

Month   EventCount
-----------------
3      | 10
4      | 20
5      | 30
6      | 40

Second query for NotificationEventLog:

SELECT MONTH(CreationDate) Month, COUNT(*) AS EventCount
FROM NotificationEventLog
WHERE YEAR(CreationDate) = 2020
GROUP BY YEAR(CreationDate), MONTH(CreationDate)
ORDER BY YEAR(CreationDate), MONTH(CreationDate)

Output:

Month   EventCount
-----------------
1      | 5
2      | 15
3      | 25
4      | 35
5      | 45
6      | 55

What I need is Sum of event count of those output as shown below. I tried to use UNION but that does not work as I expected.

Month   EventCount
-----------------
1      | 5  ==> It comes from NotificationEventLog 
2      | 15 ==> It comes from NotificationEventLog
3      | 35 ==> Sum of NotificationEvent and  NotificationEventLog (10 + 25)
4      | 55 ==> Sum of NotificationEvent and  NotificationEventLog (20 + 35)
5      | 75 ==> Sum of NotificationEvent and  NotificationEventLog (30 + 45)
6      | 95 ==> Sum of NotificationEvent and  NotificationEventLog (40 + 55)

Upvotes: 0

Views: 136

Answers (3)

Vekonomen
Vekonomen

Reputation: 66

Use CTE to get both sum and individual sum

;with cte_a as(
SELECT  MONTH(CreationDate) Month,count(*) AS EventCount
FROM NotificationEvent
WHERE YEAR(CreationDate) = 2020
GROUP BY YEAR(CreationDate),MONTH(CreationDate)
), cte_b as(
SELECT  MONTH(CreationDate) Month,count(*) AS EventCount
FROM NotificationEventLog
WHERE YEAR(CreationDate) = 2020
GROUP BY YEAR(CreationDate),MONTH(CreationDate)
), cte_month as (
Select distinct month from cte_a
Union
Select distinct month from cte_b
)
Select
M.month
, coalesce(a.eventcount, 0) as EventCount
, coalesce(b.eventcount, 0) as EventLogCount
, coalesce(a.eventcount,0) + coalesce(b.eventcount,0) as EventTotalCount

From cte_Month as m
Left join cte_a as a on a.month = m.month
Left join cte_b as b on b.month = m.month
order by m.year

Upvotes: 1

ismetguzelgun
ismetguzelgun

Reputation: 1105

You can use UNION ALL.

SELECT MONTH, SUM (COALESCE (EVENTCOUNT, 0))
    FROM (  SELECT MONTH (CREATIONDATE) MONTH, COUNT (*) AS EVENTCOUNT
              FROM NOTIFICATIONEVENT
             WHERE YEAR (CREATIONDATE) = 2020
          GROUP BY YEAR (CREATIONDATE), MONTH (CREATIONDATE)
          --order by  YEAR(CreationDate),MONTH(CreationDate)
          UNION ALL
            SELECT MONTH (CREATIONDATE) MONTH, COUNT (*) AS EVENTCOUNT
              FROM NOTIFICATIONEVENTLOG
             WHERE YEAR (CREATIONDATE) = 2020
          GROUP BY YEAR (CREATIONDATE), MONTH (CREATIONDATE)--order by  YEAR(CreationDate),MONTH(CreationDate)
         ) T
GROUP BY MONTH
ORDER BY 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

One method is a full join:

SELECT COALESCE(ne.Month, nel.Month) as month,
        (COALESCE(ne.EventCount, 0) + COALESCE(nel.EventCount, 0))
FROM (SELECT MONTH(CreationDate) as Month, count(*) AS EventCount
      FROM NotificationEvent
      WHERE YEAR(CreationDate) = 2020
      GROUP BY YEAR(CreationDate), MONTH(CreationDate)
     ) ne FULL JOIN
     (SELECT  MONTH(CreationDate) Month,count(*) AS EventCount
      FROM NotificationEventLog
      WHERE YEAR(CreationDate) = 2020
      GROUP BY YEAR(CreationDate), MONTH(CreationDate)
     ) nel
     ON ne.month = nel.month;

Upvotes: 2

Related Questions