Petite Abeille <petite.abei...@gmail.com> wrote: > On Sep 30, 2010, at 11:44 PM, Jordan Dahlke wrote: > >> Is there a good way to do this with Select and Group By statement? > > For a given definition of "good" :P > > create temporary table range as > select 0 as start, 24 as end union all > select 25 as start, 49 as end union all > select 50 as start, 74 as end union all > select 75 as start, max( value ) as end from measurement; > > > > select range.start || ' - ' || range.end as time, > sum( coalesce( speed.value, 0 ) ) as speed, > sum( coalesce( direction.value, 0 ) ) as direction, > sum( coalesce( temp.value, 0 ) ) as temp > from range > > left join measurement speed > on speed.variable = 'Speed' > and speed.time between range.start and range.end > > left join measurement direction > on direction.variable = 'Direction' > and direction.time between range.start and range.end > > left join measurement temp > on temp.variable = 'Temp' > and temp.time between range.start and range.end > > group by range.start, > range.end > > order by range.start
Rather than hardcoding table range, you can synthesize it directly from measurement. Something like this: select range.start || ' - ' || range.end as time, avg(speed.value) as speed, avg(direction.value) as direction, avg(temp.value) as temp from (select range_num*25 as start, (range_num+1)*25 as end from (select cast(time / 25 as integer) range_num from measurement group by range_num)) as range left join measurement speed on speed.variable = 'Speed' and speed.time >= range.start and speed.time < range.end left join measurement direction on direction.variable = 'Direction' and direction.time >= range.start and direction.time < range.end left join measurement temp on temp.variable = 'Temp' and temp.time >= range.start and temp.time < range.end group by range.start order by range.start; Note also that 'end' and 'temp' are keywords in SQL, you'll need to use different identifiers. Similarly, identifier 'speed' (and others) is used both as table alias and the column alias - this most likely won't fly. I didn't fix these issues in the example above, so as not to distract from the logic, but it needs these corrections before it can actually run. I'll leave this as an exercise for the reader. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users