Solved

Getting one row from a UNION

Posted on 2013-06-07
4
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

737 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