Solved

Getting one row from a UNION

Posted on 2013-06-07
4
226 Views
Last Modified: 2013-06-07
I have two select clauses with a UNION ALL and both GROUP BY employeeID

The resulting data looks like this:

Name       total1     total2
Joe Blow     3             0
Joe Blow     0             5

So Joe has 3 rows in the first select and 5 rows in the second select.

I want the final row data to look like this:

Name                  Total
Joe Blow                 8


The queries are something like this (with a lot left out where I say blah blah)...

select EmployeeName,total1, total2 from (SELECT 
LastName + ', ' + FirstName as EmployeeName , COUNT(Employee.EmployeeId) as total1, 0 as total2
From (blah blah)
Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
  UNION ALL 
SELECT 
LastName + ', ' + FirstName as EmployeeName, 0 as total1, COUNT(Employee.EmployeeId) as total2 
 from (blah blah)
 Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
) as table1
order by employeename

Open in new window


(Notice I store the count in total1 for the first select and total2 for the second select. If I stored them in the same name, the value was coming up 0 for the second select.)

Thanks!
0
Comment
Question by:Starr Duskk
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39230709
Hi Bob,

Lots of ways to do this.  Mostly you just need to collapse the two columns into 1.

select EmployeeName,total1 + total2 
from 
( SELECT LastName + ', ' + FirstName as EmployeeName , COUNT(Employee.EmployeeId) as total1, 0 as total2
  From (blah blah)
  Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
  UNION ALL 
  SELECT LastName + ', ' + FirstName as EmployeeName, 0 as total1, COUNT(Employee.EmployeeId) as total2 
  from (blah blah)
  Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
) as table1
order by employeename

Open in new window

                                 
Good Luck,
Kent
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39230716
You're not understanding. All that does it create me two rows as I already indicated are in my results.
Joe Blow 3
Joe Blow 5


I want:

Joe Blow 8
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39230723
Yeah.  My Bad.

select EmployeeName, sum (total1) + sum (total2)
from 
( SELECT LastName + ', ' + FirstName as EmployeeName , COUNT(Employee.EmployeeId) as total1, 0 as total2
  From (blah blah)
  Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
  UNION ALL 
  SELECT LastName + ', ' + FirstName as EmployeeName, 0 as total1, COUNT(Employee.EmployeeId) as total2 
  from (blah blah)
  Group by Employee.EmployeeId, UserProfile.LastName, UserProfile.Firstname
) as table1
group by employeename
order by employeename

Open in new window

                                           
And, of course, if both of the sub-queries being unioned are from the same table, we may be able to simplify this even more.

Oh.  And that also assumes that none of the values being added are NULL.  If so, the result will be NULL.  That's easy to solve though, just SUM (coalesce (value)).


Good Luck,
Kent
0
 
LVL 2

Author Comment

by:Starr Duskk
ID: 39230733
that does it. thanks!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question