Solved

Getting one row from a UNION

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

792 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