Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sum results from two different queries (SQL)

Tags:

sql

sql-server

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
like image 223
Paula C Avatar asked Aug 28 '17 16:08

Paula C


People also ask

How can I SUM the values of two queries in SQL?

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.

How do I combine two SQL queries in one result without a union?

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.


1 Answers

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
like image 146
SqlZim Avatar answered Sep 21 '22 10:09

SqlZim