Postgresql Error Aggregates Not Allowed In Where 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 aggregates not allowed in group by clause postgresql company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Aggregate Functions Are Not Allowed In Where Postgresql
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million aggregates not allowed in where clause netezza 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 1 down vote favorite I was trying to execute this
Aggregate Functions Are Not Allowed In Group By Postgres
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 < 8* AVG(ac.total) GROUP BY a.name, a.add, a.mobile; And this is the error I rails aggregate functions are not allowed in where 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,45933104183 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 * avgtotal GROUP BY name, add, mobile; share|improve this answer edited Jan 30 '14 at 20:09 answered Jan 30 '14 at 19:59 Gordon Linoff 469k20141215 Thank you Gor
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
Aggregate Functions Are Not Allowed In Variables Informatica
International Sites Propaganda Resources Weekly News Aggregates not allowed in WHERE
Django Fielderror: Aggregate Functions Are Not Allowed In This Query
clause? From: Joachim Trinkwitz
8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 8.3.23 Documentation Prev Fast Backward https://www.postgresql.org/docs/8.3/static/tutorial-agg.html Chapter 2. The SQL Language Fast Forward Next 2.7. Aggregate Functions Like most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result http://www.justskins.com/forums/aggregates-not-allowed-in-101877.html 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 not allowed 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, 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 not allowed in 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 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 thes
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