Postgresql - GROUP BY timestamp AND car_id

I've got a table with car records:

table: cars

columns:
car_id | timestamp | km_driven


I'm running PostgreSQL 9.6.

PostgreSQL 9.6

How can I do a:

SELECT timestamp, car_id, km_driven_within_the_hour FROM cars


I want to GROUP BY my timestamp so all records recorded with each hour is grouped by each car, and then have km_driven_within_the_hour SUM'ed togehter within that hour.

GROUP BY

timestamp

km_driven_within_the_hour

SUM

I've tried:

SELECT timestamp, car_id, SUM(km_driven) AS km_driven_within_the_hour
FROM cars
GROUP BY trunc(EXTRACT(hour from timestamp)), car_id


Gives me error:

ERROR: function pg_catalog.date_part(unknown, text) does not exist


And casting timestamp:

SELECT timestamp, car_id, SUM(km_driven) AS km_driven_within_the_hour
FROM cars
GROUP BY trunc(EXTRACT(hour from timestamp::timestamp)), car_id


Gives me:

ERROR: column "cars.timestamp" must appear in the GROUP BY clause or be used in an aggregate function


1 Answer
1

You're almost there. Replace the timestamp by the hour, as in:

timestamp

SELECT
trunc(EXTRACT(hour from timestamp::timestamp)),
car_id,
SUM(km_driven) AS km_driven_within_the_hour
FROM cars
GROUP BY trunc(EXTRACT(hour from timestamp::timestamp)), car_id


GROUP BY

YYYY-MM-DD HH

hour

group by date_trunc('hour', timestamp::timestamp)

group by TO_CHAR(timestamp::timestamp, 'YYYY-MM-DD HH');

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.