PostgreSQL, GROUP BY and Window function

Question

I've got the following query in PostgreSQL which works showing records:

'SELECT TO_CHAR(date_trunc(\'hour\', time::timestamp), \'YYYY-MM-DD HH24:MI:SS\') AS time_from,
        TO_CHAR(date_trunc(\'hour\', time::timestamp) + interval \'1 hour\', \'YYYY-MM-DD HH24:MI:SS\') AS time_to,
        SUM(km) AS km
 FROM cars 
 GROUP BY date_trunc(\'hour\', time::timestamp) LIMIT 1000 OFFSET 50'

I would like to add SUM(km) OVER() as km_total for getting the total amount of km as I have limit and offset in my query.

But then I get the error:

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

But I'm not allowed to add it to my GROUP BY, this gives me the following error:

error: window functions are not allowed in GROUP BY

Answer 1

You want:

   SUM(SUM(km)) OVER () AS total_km

The nesting of the SUM() looks strange at first. The inner SUM(km) is the aggregation function. The outer SUM() is the window function.