SUM Queries using UNION

Question

+------+-----------+---------------+---------+
|Period|EmOrg      |Total Hours O S|FTE_T    |
+------+-----------+---------------+---------+
|201304|BOT/GBE/T00|1067           |6.35119  |
+------+-----------+---------------+---------+
|201304|RSA/BDV/T00|352            |2.095238 |
+------+-----------+---------------+---------+
|201304|RSA/BDV/T00|374            |1.888888 |
+------+-----------+---------------+---------+
|201304|RSA/BFN/T00|198            |1        |
+------+-----------+---------------+---------+
|201304|RSA/BFN/T00|2581           |15.363095|
+------+-----------+---------------+---------+
|201304|RSA/CPT/T00|783            |3.954545 |
+------+-----------+---------------+---------+
|201304|RSA/CPT/T00|4176           |24.857142|
+------+-----------+---------------+---------+
|201304|RSA/DUR/T00|609            |3.075757 |
+------+-----------+---------------+---------+
|201304|RSA/DUR/T00|3978.5         |23.681547|
+------+-----------+---------------+---------+
|201304|RSA/JNB/T00|1545           |7.80303  |
+------+-----------+---------------+---------+
|201304|RSA/JNB/T00|6948.5         |41.360119|
+------+-----------+---------------+---------+
|201304|RSA/KNY/T00|2832           |16.857142|
+------+-----------+---------------+---------+
|201304|RSA/PLZ/T00|557            |2.813131 |
+------+-----------+---------------+---------+
|201304|RSA/PLZ/T00|3817.5         |22.723214|
+------+-----------+---------------+---------+
|201304|RSA/PTA/T00|1173           |5.924242 |
+------+-----------+---------------+---------+
|201304|RSA/PTA/T00|5813           |34.60119 |
+------+-----------+---------------+---------+
|201304|RSA/PZB/T00|5626.5         |28.416666|
+------+-----------+---------------+---------+
|201304|RSA/PZB/T00|24896.75       |148.19494|
+------+-----------+---------------+---------+
|201304|RSA/SMD/T00|1238           |7.369047 |
+------+-----------+---------------+---------+

SELECT LD.Period, LD.EmOrg,Sum(LD.RegHrs) AS 'Total Hours O S' , Sum(LD.RegHrs)/198 AS 'FTE_T' 
FROM SSI.dbo.LD LD
GROUP BY LD.Period, LD.EmOrg, LD.EmType
HAVING (LD.EmOrg Like '%T00') AND (LD.EmType='S')  
UNION
SELECT LD.Period, LD.EmOrg, Sum(LD.RegHrs) AS 'Total Hours O S' , Sum(LD.RegHrs)/168 AS 'FTE_T'
FROM SSI.dbo.LD LD
WHERE (LD.EmType In ('C','T','U')) AND (LD.EmOrg Like '%T00')
GROUP BY LD.Period, LD.EmOrg

I would really want to add my ToTal Hours O S & FTE_T to return jst one value in a single month (eg) add 201304 RSA/BDV/T00 726 and 3.984126 instead of having 2 separate values how do I go about that while I already used a Sum method and a UNION in my Query? Your assistance will really be appreaciated

Answer 1

Period  EmOrg            GrandTotal     Division
201304  BOT/GBE/T00          1067        6.1
201304  RSA/BDV/T00          726         3.9
201304  RSA/BFN/T00          2779        15.7
201304  RSA/CPT/T00          4959        27.7
201304  RSA/DUR/T00          4587.5      25.7
201304  RSA/JNB/T00          8493.5      47.3
201304  RSA/KNY/T00          2832        16.1
201304  RSA/PLZ/T00          4374.5      24.5
201304  RSA/PTA/T00          6986        39
201304  RSA/PZB/T00          30523.25    169.9
201304  RSA/SMD/T00          1238        7

This CODE works like MAGIC

SELECT 
     Period, 
     EmOrg, 
     ROUND(Sum(Total_H),2) As GrandTotal, 
     ROUND(Sum(FTE_O),1) As Division
FROM(
SELECT 
     LD.Period, 
     LD.EmOrg, 
     Sum(LD.RegHrs) AS 'Total_H', 
     Sum(LD.RegHrs)/176 AS 'FTE_O'
FROM SSI.dbo.LD LD
WHERE (LD.EmType In ('C','T','U')) AND (LD.EmOrg Like '%T00')
GROUP BY LD.Period, LD.EmOrg
UNION
SELECT 
      LD.Period, 
      LD.EmOrg, 
      Sum(LD.RegHrs) AS 'Total_H', 
      Sum(LD.RegHrs)/198 AS 'FTE_O'
FROM SSI.dbo.LD LD
WHERE (LD.EmType='S') AND (LD.EmOrg Like '%T00')
GROUP BY LD.Period, LD.EmOrg)AS counts
GROUP BY Period, EmOrg

Answer 2

I don't think you need a union for this query. You can just use conditional aggregation. Assuming the 198 in the first query is a typo:

SELECT LD.Period, LD.EmOrg,
       Sum(case when LD.EmType in (LD.EmType In ('C','T','U', 'S') then LD.RegHrs
                else 0
           end) AS "Total Hours O S" ,
       Sum(case when LD.EmType in (LD.EmType In ('C','T','U', 'S') then LD.RegHrs)/198
                else 0
           end) AS "FTE_T"
FROM SSI.dbo.LD LD
WHERE LD.EmOrg Like '%T00'
GROUP BY LD.Period, LD.EmOrg, LD.EmType;

If it is not:

SELECT LD.Period, LD.EmOrg,
       Sum(case when LD.EmType in (LD.EmType In ('C','T','U', 'S') then LD.RegHrs
                else 0
           end) AS "Total Hours O S" ,
       Sum(case when LD.EmType in (LD.EmType In ('S') then LD.RegHrs)/198
                when LD.EmType in (LD.EmType In ('C','T','U', 'S') then LD.RegHrs)/168
                else 0
           end) AS "FTE_T"
FROM SSI.dbo.LD LD
WHERE LD.EmOrg Like '%T00'
GROUP BY LD.Period, LD.EmOrg, LD.EmType;

I also changed the single quoates for the column aliases to double quotes. Although some databases support single quotes for this purpose, it is prone to error. Single quotes should only be used for string constants.