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