题解 | 2021年国庆在北京接单3次及以上的司机统计信息

SELECT
city,
ROUND(SUM(order_num)/COUNT(order_num >= '3' OR NULL), 3) AS avg_order_num,
ROUND(SUM(fare_total)/COUNT(order_num >= '3' OR NULL), 3) AS avg_income

FROM(
SELECT
city, 
driver_id,
COUNT(*) AS order_num,
SUM(fare) AS fare_total

FROM tb_get_car_record AS r
RIGHT JOIN tb_get_car_order AS o 
    USING(order_id)
    
WHERE 
city = '北京' AND 
DATE(order_time) BETWEEN '2021-10-01' AND '2021-10-07'

GROUP BY driver_id) AS t1

WHERE order_num >= '3'

GROUP BY city