ryeguy ryeguy - 1 year ago 67
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
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
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
    in the where clause, is postgres smart enough to not recalculate it?

Answer Source

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

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

EDIT: Forgot to alias subquery.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download