SQL 查询多张表的 Sum 和 Count

Question

我有以下两个表:

<强>1。 B列表

• 预订编号
• 成人号
• child 号
• 预订日期

<强>2。 B句柄

• 预订编号
• 票务状态
• 最终售价
• FinalNett
• 员工

我想要做的是让 distinct Staff 具有 Sum of (SellingPrice) , Sum of (NettPrice), Profit (Sum of sellingPrice)- Sum of (NettPrice)), No of Pax which is (AdultNo + ChildNo) 并且还将 BookingID 计算为 No of预订

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

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

这是我可以从第二张表 BHandle

得到的查询

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

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

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

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

Answer 1

像这样(假设所有列都非空):

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;