Thread: 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.