来自多个表的 Sum 和 Count 的 SQL 查询

SQL query of Sum and Count from multiple tables

提问by ymcCole

I have the following two tables:

我有以下两个表：

1. BList

1. BList

• BookingID
• AdultNo
• ChildNo
• BookingDate

• 预订编号
• 成人号
• 孩子号
• 预定日期

2. BHandle

2. 手柄

• BookingID
• TicketingStatus
• FinalSellingPrice
• FinalNett
• Staff

• 预订编号
• 售票状态
• 最终售价
• 最终网
• 职员

What I want to do is get the distinct Staffwith Sum of (SellingPrice), Sum of (NettPrice), Profit (Sum of sellingPrice)- Sum of (NettPrice)), No of Pax which is (AdultNo + ChildNo)and also count the BookingIDas No of Bookings

我想要做的就是让distinct Staff与Sum of (SellingPrice)，Sum of (NettPrice)，Profit (Sum of sellingPrice)- Sum of (NettPrice))，大同没有它(AdultNo + ChildNo)，也算BookingID为担保的无

WHERE BookingDate >= fromDate AND BookingDate <= toDate 
    AND TicketingStatus='CP'

Something that looks like this (The Total figures at the bottom doesn't matter as i will write them to csv format, i will handle the total there) but i need to figure out how to get the query first.

看起来像这样的东西（底部的总数并不重要，因为我会将它们写入 csv 格式，我将在那里处理总数）但我需要先弄清楚如何获取查询。

This is the query i can get from the 2nd Table BHandle

这是我可以从第二个表BHandle 中获得的查询

SELECT Staff, SUM(FinalSellingPrice) AS gross, SUM(FinalNett) AS cost
FROM BHandle
WHERE ticketingstatus ='CP'
GROUP BY Staff

This is my query for the 1st table BList

这是我对第一个表BList 的查询

SELECT (adultno+childno) AS pax 
fFROM om BList
WHERE bookingdate >='01-mar-2013 00:00'
AND bookingdate <= '15-may-2013 23:59'

How can I combine these 2 queries together?

如何将这两个查询组合在一起？

回答by Gareth

Something like this (assuming all columns are non null):

像这样（假设所有列都不为空）：

select Staff,
    sum(FinalSellingPrice) as gross,
    sum(FinalNett) as cost,
    sum(FinalSellingPrice - FinalNett) as profit,
    sum(AdultNo+ChildNo) as pax,
    count(1) as bookings
from Blist b
inner join BHandle bh on b.BookingID = bh.BookingID
where b.BookingDate >= fromDate
    and b.BookingDate <= toDate
    and bh.TicketingStatus = 'CP'
group by staff;

回答by Gordon Linoff

One way to do this is using union allwith an aggregation:

一种方法是使用union all聚合：

select staff, sum(gross) as gross, sum(cost) as cost, sum(pax) as pax,
       sum(numbookings) as numbookings
from ((SELECT Staff, SUM(FinalSellingPrice) AS gross, SUM(FinalNett) AS cost,
              null as pax, null as numbookings
       FROM BHandle
       WHERE ticketingstatus ='CP'
       GROUP BY Staff
      ) union all
      (select staff, null as gross, null as cost, (adultno+childno) AS pax ,
              count(*) as numbookings
       from blist join
            bhandle
            on blist.bookingid = bhandle.bookingid
       group by staff
      )
     ) t
group by staff