Using SQL UNION function

Question

Forgive the basic question but i'm relatively new to SQL. I have a couple of queries that i'd like to use the UNION function on. I think i'm having trouble ordering the items that I have to get the appropriate result, they have the some number of columns with the same names. But i'm still having issues. Here's what i'm trying to join.

    SELECT

CAST([DateTime] as DATE) as "Date"
, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SG_NAME.EnterpriseName,'UCM.',''),'_SG',''),'_en_Field',' English Field'),'_en_Tier2',' English'),'_es_Tier2',' Spanish')as "Group"
, sum(SG.CallsOffered) as "Calls Offered"
, sum(CallsHandled) as "Calls Handled"
, SUM(SG.RouterCallsAbandQ) as "Abandons"
, DATENAME(weekday,[datetime])as "Day of Week"
, DATENAME(week, [datetime]) as Week
, DATENAME(year, [datetime]) as "Year"
, DATENAME (month, [datetime]) as "Month"
, sum (SG.LoggedOnTime)/60/60 as "Hours Logged On"
--, HoldTime
FROM archive.t_Skill_Group_Interval AS SG
  INNER JOIN awdb.Skill_Group AS SG_NAME 
    ON SG.SkillTargetID=SG_NAME.SkillTargetID

    WHERE 

DATEDIFF(DAY,[DateTime],GETDATE())<180
AND DATEPART(WEEKDAY,[datetime])in (2,3,4,5,6)

AND SG.SkillTargetID in ( '5003','5007','5069','5062','5300')

group by
CAST([DateTime] as DATE) 
, SG_NAME.EnterpriseName
,DATENAME(weekday,[datetime])
, DATENAME(week, [datetime])
, DATENAME(year, [datetime])
,DATENAME (month, [datetime])
ORDER BY SG_NAME.EnterpriseName,"Date"


----UNION (I'd like to Union these two queries)

SELECT
CAST([DateTime] as DATE) as "Date"
, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(CT_NAME.EnterpriseName,'DL_',''),'_Main_CT',' Main'),'87180_',''),'_CT',''),'_Span Main','Spanish Main'),'_Switchboard_Transfer',' Switch Transfer'),'DPS_','') as "Group"
, sum(CT.CallsOffered) as "Calls Offered"
, SUM(CT.CallsHandled) as "Calls Handled"
, SUM (CT.AbandInterval1+CT.AbandInterval2+CT.AbandInterval3+CT.AbandInterval4+CT.AbandInterval5+CT.AbandInterval6+CT.AbandInterval7+CT.AbandInterval8+CT.AbandInterval9+CT.AbandInterval10) as "Abandons"
, DATENAME(weekday,[datetime])as "Day of Week"
, DATENAME(week, [datetime]) as Week
, DATENAME(year, [datetime]) as "Year"
, DATENAME (month, [datetime]) as "Month"
, SUM(CT.TalkTime) as "Hours Logged On"
--, CallsAnswered
--, a.LoggedOnTime
--, HoldTime
--, b.SkillTargetID
--, a.SkillTargetID
FROM archive.t_Call_Type_Interval AS CT
  INNER JOIN archive.t_Call_Type AS CT_NAME 
    ON CT.CallTypeID=CT_NAME.CallTypeID
WHERE 

DATEDIFF(DAY,[DateTime],GETDATE())<180
AND DATEPART(WEEKDAY,[datetime])in (2,3,4,5,6)

--DATEDIFF(DAY,[DateTime],GETDATE())<180

AND CT.CallTypeID in ('5122','5115','5285','5289','5290','5291','5288','5214')


group by
CAST([DateTime] as DATE) 
, CT_NAME.EnterpriseName
,DATENAME(weekday,[datetime])
, DATENAME(week, [datetime])
, DATENAME(year, [datetime])
,DATENAME (month, [datetime])
ORDER BY CT_NAME.EnterpriseName,"Date"

Do you get an error when you run this? In some databases I think you want to do the UNION, then do the ORDER BY at the end (rather than in each UNION branch). Also, if you know the union branches produce distinct values, use UNION ALL instead as it does not try to remove duplicates and hence is a lot less expensive with lots of distinct rows. — mc110, Jul 07 '14 at 14:48
It was ORDER BY. and UNION ALL worked great. Thanks for the help. — user3812830, Jul 07 '14 at 14:57
Added `sql-server` tag based on the usage of non-standard `[..]` "quoting". — , Jul 07 '14 at 14:57
A side note: `UNION` isn't a "function". It is called an "operator" — , Jul 07 '14 at 14:57
possible duplicate of [How to order by with union](http://stackoverflow.com/questions/4715820/how-to-order-by-with-union) — mc110, Jul 07 '14 at 15:21

score 1 · Answer 1 · answered Jul 07 '14 at 15:34

If you want to use UNION operator you have to ORDER BY at the end (rather than do that in each UNION branch). Also, if you know that UNION branches produce DISTINCT values, use UNION ALL instead as it does not try to remove duplicates and is more efficient especially when there is a lot of duplicate rows.

Using SQL UNION function

1 Answers1