CODEWITHSUNDEEP
sqlpostgresqlgroup-by
piotrek
piotrek

Reputation: 14550

postgres: select expression used in group by

i want to group by some complex expression and i want to be able to select that expression

select
  (date_part('minute',(period_start - :start)) / 6), -- problematic row
  max(high) as high
from x where ...
group by
  (date_part('minute',(period_start - :start)) / 6)

all works without the "problematic row". after adding that row, even though it's the same as the grouping by expression, i get the error:

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

Upvotes: 0

Views: 1149

Answers (1)

The Impaler
The Impaler

Reputation: 48800

I usually get rid of problems like the one you mention using CTEs (Common Table Expressions). I would compute the expression only once in the cte, and then I would use it as many times as I need it.

For example, I would rephrase your query as:

with
cte1 as (
  select
    (date_part('minute',(period_start - :start)) / 6) as mins,
    high
  from x where ...
)
select mins, max(high) as high
from cte1
group by mins

Upvotes: 2

Related Questions