On Thursday 21 February 2008 12:56 pm, Ed Leafe wrote: > Here's an SQL query that works fine in most database products, but > PostgreSQL seems to choke on it: > > select entity_type, entity_fk, count(*) as ent_count from > assignment_rotation > group by entity_type, entity_fk > having ent_count > 1 > > When I run this, I get an error: > > ERROR: column "ent_count" does not exist > SQL state: 42703 > > It seems that Postgres does not recognize column aliases. Since > 'having' applies to the result set, that alias should be present; in > fact, I can order by that alias. > > Anyone know why this happens? > > -- Ed Leafe > >From the Postgres docs for 8.2. See section in <<>>.
HAVING Clause The optional HAVING clause has the general form HAVING condition where condition is the same as specified for the WHERE clause. HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function. The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true. SELECT List The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause. << Using the clause AS output_name, another name can be specified for an output column. This name is primarily used to label the column for display. It can also be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.>> Instead of an expression, * can be written in the output list as a shorthand for all the columns of the selected rows. Also, one can write table_name.* as a shorthand for the columns coming from just that table. -- Adrian Klaver [EMAIL PROTECTED] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
