ryeguy ryeguy - 1 month ago 11
SQL Question

Referring to dynamic columns in a postgres query?

Let's say I have something like this:

select sum(points) as total_points
from sometable
where total_points > 25
group by username


I am unable to refer to
total_points
in the where clause because I get the following error:
ERROR: column "total_points" does not exist
. In this case I'd have no problem rewriting
sum(points)
in the where clause, but I'd like some way of doing what I have above.


  • Is there any way to store the result in a variable without using a stored procedure?

  • If I do rewrite
    sum(points)
    in the where clause, is postgres smart enough to not recalculate it?


Answer

I believe PostgreSQL is like other brands of sql, where you need to do:

SELECT t.* 
FROM (
    SELECT SUM(points) AS total_points
    FROM sometable
    GROUP BY username
) t
WHERE total_points > 25

EDIT: Forgot to alias subquery.