> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Sharma, Sid > Sent: Tuesday, February 24, 2009 12:47 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
