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

Gordon Linoff · Accepted Answer · 2019-04-24 13:13:01Z

6

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.

answered Apr 24, 2019 at 13:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL, GROUP BY and Window function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related