I have few records and i want to create a query to give hourly records of a each divece battery level
What i did from timestamp field i extract date and select min function to get low value but as extract hour from timestamp is not aggragate function so i need to add in group by which now given me duplicate records.
Here is my sql:
select extract(hour from observationtime) as hour,
min(batterylevel) as batterylevel
and observationtime between '2016-02-02' and '2016-03-02'
group by observationtime,deviceid
order by observationtime ASC, deviceid ASC
Change Group by column Order to first Group by
deviceid then by the
hour using the same function
extract(hour from observationtime).
SELECT deviceid, extract(hour from observationtime) AS hour, min(batterylevel) AS batterylevel FROM smartvakt_device_report WHERE batterylevel!='' AND deviceid!='' AND observationtime BETWEEN '2016-02-02' AND '2016-03-02' GROUP BY deviceid, extract(hour from observationtime) ORDER BY extract(hour from observationtime) ASC, deviceid ASC