MySQL:获取计数和平均值

Question

这个问题已经有答案了:

multiple query same table but in different columns mysql (4 个回答)

已关闭 6 年前。

select 
COUNT(pd.property_id) AS `Beginning Total File Count`,
COUNT(pd.recv_dt) as `average days in inventory`,
AVG(pd.status = 'P') as `average days in pre-marketing`,
AVG(pd.status NOT IN('I','C')) as `average days onMarket`,
AVG(pd.status ='U') as `average days UnderContract`,
SUM(pd.status = 'O') as `Total FilesOccupied Status`,
SUM(pd.status = 'O') / COUNT(pd.property_id) as `percentage of Occupied / 
total file count`
from resnet.property_Details pd

我正在努力获取

1. 开始总文件数
2. 平均库存天数
3. 营销前的平均天数
4. 平均上市天数
5. 平均契约(Contract)天数
6. 处于占用状态的文件总数
7. 占用百分比/文件总数

不确定我的查询是否写得正确，请帮忙:)

Answer 1

好吧，作为一个非常疯狂的猜测，您正在追求这个请求:

select 
COUNT(distinct pd.property_id) AS `Beginning Total File Count`,
COUNT(pd.recv_dt) as `average days in inventory`,
AVG(IF(pd.status = 'P', 1,0)) as `average days in pre-marketing`,
AVG(IF(pd.status NOT IN('I','C'), 1,0)) as `average days onMarket`,
AVG(IF(pd.status ='U', 1,0)) as `average days UnderContract`,
SUM(IF(pd.status = 'O', 1,0)) as `Total FilesOccupied Status`,
SUM(IF(pd.status = 'O', 1,0)) / COUNT(pd.property_id) as `percentage of Occupied / 
total file count`
from resnet.property_Details pd

但是如果您不使用 group by 语句，那么使用 AVG() 似乎很奇怪...(它要么只是一个条件总和，要么您需要按某物分组)