Referring to dynamic columns in a postgres query?

Question

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 1

SELECT  SUM(points) AS total_points
FROM    sometable
GROUP BY
        username
HAVING  SUM(points) > 25

PostgreSQL won't calculate the sum twice.

Answer 2

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.