Solved

Getting one row from a UNION

Posted on 2013-06-07
4
228 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 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