SQL - 在 SELECT 中组合筛选和非筛选字段

Question

my_table

id  model  datetime     driver   distance
---|-----|------------|--------|---------
1  | S   | 04/03/2009 | john   | 399 
2  | X   | 04/03/2009 | juliet | 244
3  | 3   | 04/03/2009 | borat  | 555
4  | 3   | 03/03/2009 | john   | 300
5  | X   | 03/03/2009 | juliet | 200
6  | X   | 03/03/2009 | borat  | 500
7  | S   | 24/12/2008 | borat  | 600
8  | X   | 01/01/2009 | borat  | 700

所需输出

model  all_trips     driver   distance
-----|------------|--------|---------
  3  | 2          | borat  | 555
  X  | 4          | borat  | 1200
  S  | 2          | borat  | 600

到目前为止我的查询

SELECT
model AS model, driver AS driver
SUM(distance) as total_distance
FROM my_table
WHERE driver = 'borat'
GROUP BY model

输出

model  driver   total_distance
-----|--------|--------
 3   | borat  | 555
 X   | borat  | 1200
 S   | borat  | 600

我需要帮助来获取all_trips列，all_trips其中引用每个模型的未过滤记录计数（即没有过滤器WHERE driver = 'borat'）

Answer 1

你快到了。只需要加入每个模型的计数。

select m.model,t.cnt as all_trips,m.distance,m.driver 
from (SELECT model,driver,SUM(distance) as distance
      FROM my_table
      WHERE driver = 'borat'
      GROUP BY model,driver) m
JOIN (select model,count(*) as cnt from my_table group by model) t  on t.model=m.model