jeudi 21 avril 2016

SQL - combining filtered and non-filtered fields in SELECT

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

required output

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

my query so far

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

output

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

I need help to get the all_trips column where all_trips refer to the unfiltered count of records per model (ie. without the filter WHERE driver = 'borat')

Aucun commentaire:

Enregistrer un commentaire