[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

Union Two Columns and Sum in Same Table?

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
pointeman
Asked:
pointeman
  • 4
  • 2
1 Solution
 
chapmandewCommented:
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
 
pointemanAuthor Commented:
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
 
chapmandewCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pointemanAuthor Commented:

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
 
SharathData EngineerCommented:
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
 
pointemanAuthor Commented:
Yours doesn't work correctly either.
I've continued with the query and found the solution.
 
0
 
pointemanAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now