View source for Sql of note/Pivoting in sql


thdata:

<pre>
sqlite> select * from thedata limit 10;
collecttime|sensor1|sensor2
1525694702|685|255
1525695002|694|262
1525695302|706|267
1525695602|706|269
1525695902|700|267
1525696202|700|266
1525696502|697|264
1525696802|695|261
1525697102|694|259
1525697402|698|261
</pre>

How many record for each hour ?

<pre>
SELECT count(*), hour
from (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    group by day , hour
)
group by hour;
</pre>


Intermediate step:

<pre>
select day,
 CASE WHEN hour == '21' THEN theavg ELSE 0 END AS twentyone
 FROM (
    SELECT
    date ( datetime(collecttime, 'unixepoch', 'localtime') ) as day,
    strftime('%H', datetime(collecttime, 'unixepoch', 'localtime') ) as hour,
    avg(sensor1) as theavg
    from thedata
    where 
      date ( datetime(collecttime, 'unixepoch', 'localtime') ) == '2018-10-25'
    group by day, hour
    )
</pre>