Error Aggregates Not Allowed In Group By Clause
Contents |
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 aggregates not allowed in group by clause postgresql Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation aggregates not allowed in where clause netezza Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just aggregate function or the group by clause like you, helping each other. Join them; it only takes a minute: Sign 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
Aggregate Functions Are Not Allowed In Group By Postgres
get ERROR: aggregate functions are not allowed in WHERE SELECT o.ID , count(p.CAT) FROM Orders o 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 aggregate functions are not allowed in where postgres 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 GROUP BY o.ID HAVING count(p.CAT) > 3; HAVING is similar to WHERE, that is both are used to filter the resulting records but HAVING is used to filter on aggregated data (when GROUP BY is used). share|improve this answer edited Jan 8 '14 at 9:41 answered Jan 8 '14 at 9:35 Aziz Shaikh 11.5k73753 add a comment| up vote 3 down vote Use HAVING clause instead of WHERE Try this: SELECT o.ID, COUNT(p.CAT) cnt FROM Orders o INNER JOIN Products p ON o.P_ID = p.P_ID GROUP BY o.ID HAVING cnt > 3 share|improve this answer edited Jan 8 '14 at 9:43 answered Jan 8 '14 at 9:37 Saharsh Shah 21.4k52356 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged mysql sql group-by syntax-error aggregate-functions or ask your own question. asked 2 years ago viewed 11371 times active 2 years ago Get
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 column must appear in the group by clause or be used in an aggregate function postgresql about Stack Overflow the company Business Learn more about hiring developers or posting
Rails Aggregate Functions Are Not Allowed In Where
ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack
Aggregate Functions Are Not Allowed In Variables Informatica
Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR: Aggregates not allowed in WHERE clause in postgreSQL up vote http://stackoverflow.com/questions/20991729/how-to-avoid-error-aggregate-functions-are-not-allowed-in-where 1 down vote favorite I was trying to execute this following query, and I am a beginner in writing SQL Queries, was wondering how can I achieve the following aggregate functions functionality doing in WHERE clause. Any help on this would be a great learning curve for me. select a.name,a.add,a.mobile from user a INNER JOIN user_info aac ON aac.userid= a.userid INNER JOIN info ac ON aac.infoid= ac.infoid WHERE a.total http://stackoverflow.com/questions/21466123/error-aggregates-not-allowed-in-where-clause-in-postgresql < 8* AVG(ac.total) GROUP BY a.name, a.add, a.mobile; And this is the error I am getting in PostgreSQL: ERROR: aggregates not allowed in WHERE clause LINE 1: ...infoid = ac.infoid where a.total < 8* AVG(ac.tot... ^ ********** Error ********** ERROR: aggregates not allowed in WHERE clause SQL state: 42803 Character: 190 Am I suppose to use Having clause to have the results? any correction on this would be a great help! sql postgresql join aggregate-functions share|improve this question asked Jan 30 '14 at 19:48 AKIWEB 4,44933104183 3 You want the having clause: postgresql.org/docs/current/static/tutorial-agg.html –a_horse_with_no_name Jan 30 '14 at 19:50 possible duplicate of rails 3, getting a PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects –bluefeet♦ Jan 30 '14 at 19:58 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted You can do this with a window function in a subquery: select name, add, mobile from (select a.name, a.add, a.mobile, total, avg(ac.total) over (partition by a.name, a.add, a.mobile) as avgtotal, a.total from user a INNER JOIN user_info aac ON aac.userid= a.userid INNER JOIN info ac ON aac.infoid= ac.infoid ) t WHERE total < 8 * avgt
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 8.3.23 Documentation Prev Fast Backward Chapter https://www.postgresql.org/docs/8.3/static/tutorial-agg.html 2. The SQL Language Fast Forward Next 2.7. Aggregate Functions Like http://www.justskins.com/forums/aggregates-not-allowed-in-101877.html most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple 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 not allowed 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, 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 not allowed in 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 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_l
Hi All, I have a table (table info) with 2 column, column 'name' and column 'quantity'. name | quantity -------------------------- a | 5 b | 3 a | 3 c | 4 b | 6 If I want to sum the sum of all entry in table. name | quantity ------------------------ a | 8 b | 9 c | 4 I can use : select name, sum(quantity) from info group by name; I want to show the sum of all entry that bigger than 5 name | quantity ------------------------ a | 8 b | 9 I try to use : ... Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode December 16th,05:02 AM #1 Aggregates not allowed in WHERE clause? Hi All, I have a table (table info) with 2 column, column 'name' and column 'quantity'. name | quantity -------------------------- a | 5 b | 3 a | 3 c | 4 b | 6 If I want to sum the sum of all entry in table. name | quantity ------------------------ a | 8 b | 9 c | 4 I can use : select name, sum(quantity) from info group by name; I want to show the sum of all entry that bigger than 5 name | quantity ------------------------ a | 8 b | 9 I try to use : select name, sum(quantity) from info where sum(quantity)>20 group by name; This yields the message: 'Aggregates not allowed in WHERE clause'. Can somebody help me here.... thx, Frans ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url] Frans Guest December 16th,05:17 AM #2 Re: Aggregates not allowed in WHERE clause? Frans