jeudi 5 mai 2016

SQL - aggregating across multiple rows

my_table

I have the following table which has the drivers and riders details captured. For each day( datetime) there is one driver and zero or more riders. If there are more than one rider, for each rider the data ( name of rider and age of rider) is captured in a new row with the same datetime. This may not be the right way to structure the data, but it is so primarily due to the varying number of riders per driver per datetime

id    datetime    driver   age    riders   rider_name | rider_age
---|------------|--------|------|--------|------------|---
1  | 03/03/2009 | joe    | 24   | 0      |            | 
2  | 04/03/2009 | john   | 39   | 1      | juliet     | 30
3  | 05/03/2009 | borat  | 32   | 2      | jane       | 45
4  | 05/03/2009 |        |      |        | mike       | 18
5  | 06/03/2009 | john   | 39   | 3      | duke       | 42
6  | 06/03/2009 |        |      |        | jose       | 33
7  | 06/03/2009 |        |      |        | kyle       | 24

required output

For each datetime value, need the driver, age, number of riders, name of youngest rider and number of riders within +/- 10 years of the driver

 datetime    driver   age    riders   youngest_rider  riders_within_ten_years_of_driver
------------|--------|------|--------|--------------|---
 03/03/2009 | joe    | 24   | 0      |              | 0        # no rider
 04/03/2009 | john   | 39   | 1      | juliet       | 1        # juliet
 05/03/2009 | borat  | 32   | 2      | mike         | 0        # no rider
 06/03/2009 | john   | 39   | 3      | kyle         | 2        # duke, jose

Aucun commentaire:

Enregistrer un commentaire