Solved

# Union Two Columns and Sum in Same Table?

Posted on 2008-10-31
1,070 Views
Don't know how to successfully union these seperate queries. These two queries return a sum of counts per user, either In or Out

Select UserOut, sum(Sent) as [sent], sum(rcvd) as [Received] from table1 t1
Join table2 on UserName = t1.UserOut
Group By UserOut

Select UserIn, sum(Sent) as [sent], sum(rcvd) as [Received] from table1 t1
Join table2 on UserName = t1.UserIn
Group By UserIn
0
Question by:pointeman
[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
• 4
• 2

LVL 60

Expert Comment

ID: 22852824
try this:

Select UserOut, sum(Sent) as [sent], sum(rcvd) as [Received], 'Out' as direction
from
table1 t1
Join table2 on UserName = t1.UserOut
Group By UserOut
union all
Select UserIn, sum(Sent) as [sent], sum(rcvd) as [Received], 'In'
from table1 t1
Join table2 on UserName = t1.UserIn
Group By UserIn
0

Author Comment

ID: 22853311
That's better, but I still have dups like so: (maybe a union is not the answer)
[Result]
Bob    555    1221
Bob    333      333
Joe         0           0
Joe         0           0
0

LVL 60

Expert Comment

ID: 22853356
from
(
Select UserOut, sum(Sent) as [sent], sum(rcvd) as [Received]
from
table1 t1
Join table2 on UserName = t1.UserOut
Group By UserOut
union all
Select UserIn, sum(Sent) as [sent], sum(rcvd) as [Received]
from table1 t1
Join table2 on UserName = t1.UserIn
Group By UserIn
) a
group by userout
0

Author Comment

ID: 22853582

Removing , sum(sent), sum(received) from the first row removed the error, but also returns the following:

Error:
Invalid column name 'sent'.
Invalid column name 'rcvd'.

Return:
Bob
Joe

select userout
from
(
Select UserOut, sum(Sent) as [sent], sum(rcvd) as [Received]
from
table1 t1
Join table2 on UserName = t1.UserOut
Group By UserOut
union all
Select UserIn, sum(Sent) as [sent], sum(rcvd) as [Received]
from table1 t1
Join table2 on UserName = t1.UserIn
Group By UserIn
) a
group by userout
0

LVL 41

Expert Comment

ID: 22909623
Why did you remove that from chapmandew's query. chapmandew's query should give you the desired result. I did a minor modiification to his solution.

select a.user,sum(a.sent),sum(a.rcvd)
from
(
Select UserOut as user, sum(Sent) as [sent], sum(rcvd) as [Received]
from
table1 t1
Join table2 on UserName = t1.UserOut
Group By UserOut
union all
Select UserIn as user, sum(Sent) as [sent], sum(rcvd) as [Received]
from table1 t1
Join table2 on UserName = t1.UserIn
Group By UserIn
)  as a
group by a.user
0

Author Comment

ID: 22911405
Yours doesn't work correctly either.
I've continued with the query and found the solution.

0

Accepted Solution

pointeman earned 0 total points
ID: 22911414
Here is the correct method I've found: (had to change the Join to a Where clause)

Select UserOut, sum(Sent) as [sent], sum(Rcvd) as [received]
From
(
Select UserOut, Sent, Rcvd From table1 Where Exists
(
)
Union All
Select UserIn, Sent, Rcvd From table1 Where Exists
(
)
)
as tmp Group By UserOut
0

## Featured Post

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month3 days, 3 hours left to enroll