Query question

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Sharma, Sid
Sent: Tuesday, February 24, 2009 12:47 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Query question

Hi,

I am new to Postgres and am trying to write a query like the one below
(without any luck)

SELECT col_speed, col_time, (col_speed / col_time) AS distance
FROM speed_ratings
HAVING distance > ?
ORDER BY distance

In other words, I want to filter on a calculated column. But I get an
error that column distance is not defined
column "distance" does not exist at character 272

Interestingly if I remove the filter (HAVING distance > ?), the query
works. So I can sort on distance but not filter.
I have tried substituting the HAVING clause with a WHERE clause as well
with no luck.
I have also added a GROUP BY clause with the HAVING as well with no
luck.

Any ideas?
Thanks
Sid

You were on the right track, unfortunately the rules are not very
Consistent regarding when aliases can or cannot be used. In this case,
WHERE and HAVING cannot use an alias, but ORDER BY and most others
require it. Also, HAVING is applied to aggregate functions (like
min/max/average) Try your query in this form:

SELECT col_speed, col_time, (col_speed / col_time) AS distance
FROM speed_ratings
WHERE (col_speed / col_time) > ?
ORDER BY dd

If you want to use GROUP BY / HAVING, you need to use another field
to group the results by, as well as the aggregate function. for
example, if you had a "type_id" field and wanted the maximum
distance travelled per type:

SELECT type_id, MAX(col_speed / col_time) AS max_distance
FROM speed_ratings
GROUP BY type_id
HAVING MAX(col_speed / col_time) > ?
ORDER BY dd

Finally, if you really want distance, I assume you mean speed * time,
not speed/time.