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
                SQL SUM() function Applies to all values. Return the SUM of unique values. Expression made up of a single constant, variable, scalar function, or column name. The expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
You need to create two separate queries and join their result not JOIN their tables. Show activity on this post. JOIN and UNION are differents. In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN.
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
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With