Combining Union results

Question

I have the below SQL Query

  select Count(emailID) as ViewsThatMonth, 
         Day(entry_date) as day, 
         Month(entry_date) as month, 
         Year(entry_date) as year 
    from email_views
   where emailID = 110197
Group By Day(entry_date), Month(entry_date), Year(entry_date)
UNION ALL 
  select Count(emailID) as ViewsThatMonth, 
         Day(Record_Entry) as day, 
         Month(Record_Entry) as month, 
         Year(Record_Entry) as year 
    from dbo.tblOnlineEmail_Views
   where emailID = 110197
Group By Day(Record_Entry), Month(Record_Entry), Year(Record_Entry)
order by 4, 3, 2

The results are showing as below. I need the results on the same date to be combined. I.e. the total for the 23/8/2010 should be 800.

ViewsThatMonth  day  month  year 
---------------------------------
799             23   8      2010
1               23   8      2010
281             24   8      2010
88              25   8      2010
1               25   8      2010

Answer 1

Basically the easiest way is to make your union a derived table or CTE and then group them by date.

IE.

   select 
sum(dt.ViewsThatMonth) as ViewsThatMonth
,dt.[day]
,dt.[month]
,dt.[year]

from
(select Count(emailID) as ViewsThatMonth, Day(entry_date) as day, Month(entry_date) as month, Year(entry_date) as year from email_views
where emailID = 110197
Group By Day(entry_date), Month(entry_date), Year(entry_date)

UNION ALL 

select Count(Record_Entry) as ViewsThatMonth, Day(Record_Entry) as day, Month(Record_Entry) as month, Year(Record_Entry) as year from dbo.tblOnlineEmail_Views
where emailID = 110197
Group By Day(Record_Entry), Month(Record_Entry), Year(Record_Entry)
) dt

group by  [day], [month], [year]

order by dt.[year], dt.[month], dt.[day]

Answer 2

Keeping UNIONed code to a minimum:

select Count(emailID) as ViewsThatMonth, 
         Day(sort_date) as day, 
         Month(sort_date) as month, 
         Year(sort_date) as year 
    from (select v.*, 
          case c.caseid when 1 then entry_date else record_entry end sort_date
          from email_views v
          cross join (select 1 caseid union all select 2 caseid) c
          where v.emailID = 110197) sq
Group By Day(sort_date), Month(sort_date), Year(sort_date)

EDIT: Added alias to subquery

Answer 3

You only need to group by once:

SELECT Count(emailID) as ViewsThatMonth, 
         Day(entry_date) as day, 
         Month(entry_date) as month, 
         Year(entry_date) as year
from(
select emailID, Record_Entry AS entry_date 
    from email_views
   where emailID = 110197
UNION ALL 
  select emailID, entry_date 
    from dbo.tblOnlineEmail_Views
   where emailID = 110197
) AS t
Group By Day(entry_date), Month(entry_date), Year(entry_date)
order by 4, 3, 2