group by on union all result SQL Server

Question

I am new to SQL Server. I have a SQL query where I performed an union all, the 2 individual queries have group by.

select top 5  
    Starttime, convert(date,row_date) as Date,
    sum(acdcalls + abncalls) [Offered],
    sum(acdcalls) [Handled],
    sum(abncalls) [Abandoned],
    sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5) [Answered within SLA],
    case 
        when sum(acdcalls) != 0 
          then cast((sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5)) * 1.0 / sum((acdcalls)) * 1.0 * 100 as decimal(10, 2)) 
          else 0 
    end as [SLA in %]
from 
    db1
where 
    row_date = getdate()
group by 
    Starttime, row_Date

union all

select top 5 
    Starttime, convert(date,row_date) as Date,
    sum(acdcalls + abncalls) [Offered],
    sum(acdcalls) [Handled],
    sum(abncalls) [Abandoned],
    sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5) [Answered within SLA],
    case 
       when sum(acdcalls) != 0 
         then cast((sum(acdcalls1 + acdcalls2 + acdcalls3 + acdcalls4 + acdcalls5)) * 1.0 / sum((acdcalls)) * 1.0 * 100 as decimal(10, 2)) 
         else 0 
    end as [SLA in %]
from 
    db2
where 
    row_date = getdate()
group by 
    Starttime, row_Date

Starttime column has common values. I want to do group by Starttime for the result. How can I do that? Any help would be much appreciated

score 5 · Accepted Answer · answered May 01 '15 at 18:59

You need to do the union first, then aggregate. The following example uses a subquery, but you can use a temp table instead if you prefer:

Select StartTime, Row_Date, sum(acdcalls+abncalls)...[other sums here]
  From (
    select * from db1 
    union all
    select * from db2
  ) a
group by StartTime, RowDate

You can still have your where clauses and your specific columns in the subquery if necessary (the example above will only work if db1 and db2 have the same columns in the same order - otherwise you will need to specify your columns). I am not sure why you want to group by Row_Date if you are limiting both of your selects to Row_Date = GetDate(), though.

group by on union all result SQL Server

1 Answers1