Referring to dynamic colums 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.

Answer 3

You have error in statement:

select sum(points) as total_points
from sometable
where total_points > 25 -- <- error here
group by username

You can't limit rows by total_points, because sometable don't have that column. What you want is limit gouped resulting rows by total_points, computed for each group, so:

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

If you replace total_point in your example, then you simply chech if sum computed from all rows is bigger than 25 and then return all rows, grouped by username.

Edit:
Always remember order:

1. is FROM with JOIN's to get tables
2. is WHERE for limit rows from tables
3. is SELECT for limit columns
4. is GROUP BY for group rows into related groups
5. is HAVING for limit resulting groups
6. is ORDER BY for order results