CODEWITHSUNDEEP
sqlpostgresql
ryeguy
ryeguy

Reputation: 66911

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.

Upvotes: 19

Views: 8522

Answers (3)

MBO
MBO

Reputation: 31025

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

Upvotes: 9

Nathan Wheeler
Nathan Wheeler

Reputation: 5932

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.

Upvotes: 12

Quassnoi
Quassnoi

Reputation: 425833

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

PostgreSQL won't calculate the sum twice.

Upvotes: 26

Related Questions