Solved

Getting one row from a UNION

Posted on 2013-06-07
4
224 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:Kdo
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:
Kdo 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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