Petite Abeille <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
