SQL查询多个表的总和和计数 [英] SQL query of Sum and Count from multiple tables

问题描述

我有以下两个表：

1。 BList

• BookingID


• AdultNo


• ChildNo


• BookingDate

• BookingID
• AdultNo
• ChildNo
• BookingDate

2。 BHandle

• BookingID


• TicketingStatus


• FinalSellingPrice


• FinalNett


• 工作人员

• BookingID
• TicketingStatus
• FinalSellingPrice
• FinalNett
• Staff

我想做的是得到与众不同的员工，其中（SellingPrice）之和，总和（净价），利润（卖价总和）-（净价）的总和），没有乘数的（ AdultNo + ChildNo），还将 BookingID 计为预订数量

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

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

看起来像这样（底部的总数无所谓，因为我会将其写入csv格式，我将在那里处理总数），但我需要弄清楚如何获得首先查询。

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.

这是查询ic从第二张表中获得 BHandle

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

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

这是我查询的第一张表 BList

This is my query for the 1st table 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?

推荐答案

这样的事情（假设所有列都不为空）：

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;

这篇关于SQL查询多个表的总和和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！