CODEWITHSUNDEEP
sqlpostgresql
Alfred Balle
Alfred Balle

Reputation: 1195

PostgreSQL, GROUP BY and Window function

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

Upvotes: 3

Views: 1535

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 6

Related Questions