sql - 两个不同查询 (SQL) 的结果求和

Question

我有来自两个不同表的两个查询。一种是使用 SUM 函数，另一种是使用 COUNT 函数。我需要的是对它们的结果进行求和，这样我就可以获得一张包含总记录的表(例如表“C”)。

到目前为止，我已经尝试过这种连接，但它不起作用:

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 

表 A(收到的联系人)

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

   Origin Count(*)
   Email    500
   Phone    200 
   Social   100

表 B(已发送联系人)

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

   Origin Sum(*)
   Email    20
   Phone   100

表 C(联系人总数)

   Origin Total
   Email   520
   Phone   300
   Social  100

Answer 1

您可以联合派生表/子查询中的每个计数查询，如下所示:

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