error aggregates not allowed in where clause postgres
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads aggregate functions are not allowed in where postgres with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack aggregates not allowed in where clause netezza Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign aggregate functions are not allowed in group by postgres up How to avoid error “aggregate functions are not allowed in WHERE” up vote 13 down vote favorite 2 With the follow sql code, i get ERROR: aggregate functions are not allowed in WHERE SELECT o.ID , count(p.CAT) FROM Orders o rails aggregate functions are not allowed in where INNER JOIN Products p ON o.P_ID = p.P_ID WHERE count(p.CAT) > 3 GROUP BY o.ID; How can I avoid this error? mysql sql group-by syntax-error aggregate-functions share|improve this question edited Jan 8 '14 at 9:38 Saharsh Shah 21.4k52356 asked Jan 8 '14 at 9:34 D-Lef 1062615 add a comment| 2 Answers 2 active oldest votes up vote 24 down vote accepted Replace WHERE clause with HAVING, like this: SELECT o.ID , count(p.CAT) FROM Orders o INNER JOIN Products p ON o.P_ID = p.P_ID
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces
Aggregate Functions Are Not Allowed In Join Conditionspgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer column must appear in the group by clause or be used in an aggregate function postgresql lists Regional lists Associations User groups Project lists Inactive lists django aggregate functions are not allowed in this query IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Re: Aggregates in WHERE clause? From: http://stackoverflow.com/questions/20991729/how-to-avoid-error-aggregate-functions-are-not-allowed-in-where "Igor Neyman"
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 8.3.23 Documentation Prev Fast Backward Chapter 2. The SQL Language Fast Forward Next 2.7. Aggregate Functions Like most other relational database https://www.postgresql.org/docs/8.3/static/tutorial-agg.html products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple https://firstname.lastname@example.org input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows. As an example, we can find the highest low-temperature reading anywhere with: SELECT max(temp_lo) FROM weather; max ----- 46 (1 row) If we wanted to know what city (or cities) that reading occurred in, not allowed we might try: SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG but this will not work since the aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is often the case the query can be restated to accomplish the desired not allowed in result, here by using a subquery: SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); city --------------- San Francisco (1 row) This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query. Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the maximum low temperature observed in each city with: SELECT city, max(temp_lo) FROM weather GROUP BY city; city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows) which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped rows using HAVING: SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40; city | max ---------+----- Hayward | 37 (1 row) which gives us the same results for only the cities that have all temp_lo values below 40. Finally, if we only care about cities whose names begin with "S", we might do: SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40; (1) The LIKE operator does pattern matching and is explained in Section 9.7. It is important to understand the interaction between aggregates and SQ
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Aggregates in WHERE clause? From: "Ruben Gouveia"