Solved

Union Two Columns and Sum in Same Table?

Posted on 2008-10-31
8
1,062 Views
Last Modified: 2012-06-21
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
Comment
Question by:pointeman
  • 4
  • 2
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
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

by:pointeman
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

by:chapmandew
ID: 22853356
select userout, sum(sent), sum(received)
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
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.

 

Author Comment

by:pointeman
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

by:Sharath
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

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

Accepted Solution

by:
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
(
Select UserName From table2 Where UserName = UserOut
)
Union All
Select UserIn, Sent, Rcvd From table1 Where Exists
(
Select UserName From table2 Where UserName = UserIn
)
)
as tmp Group By UserOut
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 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