CODEWITHSUNDEEP
sqlsql-server
Paula C
Paula C

Reputation: 194

Sum results from two different queries (SQL)

I have two queries from two different tables. One is made using SUM function and the other was made using COUNT function. What I need is to sum their results so I can get one table with total records (such as Table "C").

So far, I've tried this join but it is not working:

select a.origin, count(*) as received, sum(b.contacts) as sent
from bd.received a 
left join db.sent b
on a.origin=b.origin
group by b.origin

Table A (Received Contacts)

select count(*), origin from db.received group by origin

   Origin Count(*)
   Email    500
   Phone    200 
   Social   100

Table B (Sent Contacts)

select sum(contacts), origin from db.sent group by origin

   Origin Sum(*)
   Email    20
   Phone   100

Table C (Total Contacts)

   Origin Total
   Email   520
   Phone   300
   Social  100

Upvotes: 7

Views: 4365

Answers (3)

Janardhan Maithil
Janardhan Maithil

Reputation: 81

This should do the job

select origin,
       sum(count) 
from (
      select * from rec 
      union all
      select * from  sent) 
group by  
origin

Here the link http://www.sqlfiddle.com/#!4/0a8fc/1

Upvotes: 0

Rahul Richhariya
Rahul Richhariya

Reputation: 514

you can use below query - 

select t1.origin , sum(received) from 
(
select a.origin, count(*) received 
from db.received a 
group by a.origin 
union all
select b.origin, count(*) sent 
from db.sent b 
group by b.origin 
) as t1 
group by t1.origin

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You could union all each counting query in a derived table/subquery like so:

select 
    origin
  , Received = sum(ReceivedCount)
  , Sent     = sum(SentCount)
  , Total    = sum(ReceivedCount)+sum(SentCount)
from (
  select origin, ReceivedCount = count(*), SentCount=0
  from bd.received
  group by origin
  union all
  select origin, ReceivedCount = 0, SentCount=count(*)
  from db.sent
  group by origin
  ) s
group by origin

Upvotes: 9

Related Questions