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
Aggregate Functions Are Not Allowed In Where Postgres
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges aggregates not allowed in where clause netezza Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each
Aggregate Functions Are Not Allowed In Group By Postgres
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 get ERROR: aggregate functions are rails aggregate functions are not allowed in where 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 vote accepted Replace WHERE clause with HAVING, like aggregate functions are not allowed in variables informatica 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 Linked 0 posgresql query to check minimum and maximum data Related 1Aggregate functions - getting columns not
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
Aggregate Functions Are Not Allowed In Join Conditions
groups Project lists Inactive lists IRC Local User Groups column must appear in the group by clause or be used in an aggregate function postgresql Featured Users International Sites Propaganda Resources Weekly News Aggregates not allowed in WHERE clause?
Django Aggregate Functions Are Not Allowed In This Query
From: Joachim Trinkwitz To: pgsql-sql(at)postgresql(dot)org Subject: Aggregates not allowed in WHERE clause? Date: 2002-06-19 08:04:24 Message-ID: 874rfzr8xj.fsf@waffel.germanistik.uni-bonn.de (view raw or whole thread) Thread: http://stackoverflow.com/questions/20991729/how-to-avoid-error-aggregate-functions-are-not-allowed-in-where 2002-06-19 08:04:24 from Joachim Trinkwitz 2002-06-19 08:13:28 from "Christopher Kings-Lynne" 2002-06-19 08:18:13 from Achilleus Mantzios 2002-06-19 10:18:38 from Christoph Haller 2002-06-20 08:05:29 from Joachim Trinkwitz Lists: pgsql-sql Hi all, I have a table (lv) with a field "semester" and I'm trying to https://www.postgresql.org/message-id/874rfzr8xj.fsf@waffel.germanistik.uni-bonn.de fish out all rows which have a value corresponding to a max() value of another table's (lf_sem) "semester" field. The intention is to keep a value with the current term in lf_sem so I can get all rows which concern this term. Example tables: lv == semester | kvvnr ---------+------ 2001ss | 4185 2001ss | 4203 2002ws | 4163 2002ws | 4190 lf_sem ====== semester -------- 2001ws 2002ss 2002ws At first I tried this query: SELECT kvvnr FROM lv, lf_sem WHERE lv.semester = max(lf_sem.semester); This yields the message: 'Aggregates not allowed in WHERE clause'. Next I tried this one: SELECT kvvnr, max(lf_sem.semester) AS akt_semester FROM lv, lf_sem WHERE lv.semester = akt_semester; Now I got: 'Attribute 'akt_semester' not found' Is there another way to get what I want? Clueless, joachim Responses Re: Aggregates not allowed in WHERE clause? at 2002-0
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 https://www.postgresql.org/message-id/e07d6ffa04121521025101d985@mail.gmail.com lists Inactive lists IRC Local User Groups Featured Users International https://www.postgresql.org/message-id/51e507b0809101611x230bf7a6if0da0760108986ea@mail.gmail.com Sites Propaganda Resources Weekly News Aggregates not allowed in WHERE clause? From: Frans To: pgsql-general(at)postgresql(dot)org Subject: Aggregates not allowed in WHERE clause? Date: 2004-12-16 05:02:34 Message-ID: e07d6ffa04121521025101d985@mail.gmail.com (view raw or whole thread) Thread: 2004-12-16 05:02:34 from Frans not allowed 2004-12-16 05:17:49 from Tom Lane 2004-12-16 05:37:19 from Michael Fuhr 2004-12-16 06:22:45 from Christopher Browne Lists: pgsql-general Hi All, I have a table (table info) with 2 column, column 'name' and column 'quantity'. name | quantity -------------------------- a | 5 b | 3 a | 3 not allowed in 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 Responses Re: Aggregates not allowed in WHERE clause? at 2004-12-16 05:17:49 from Tom Lane Re: Aggregates not allowed in WHERE clause? at 2004-12-16 05:37:19 from Michael Fuhr Re: Aggregates not allowed in WHERE clause? at 2004-12-16 06:22:45 from Christopher Browne pgsql-general by date Next:From: Ron PetersonDate: 2004-12-16 05:1
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" To: pgsql-sql Subject: Aggregates in WHERE clause? Date: 2008-09-10 23:11:13 Message-ID: 51e507b0809101611x230bf7a6if0da0760108986ea@mail.gmail.com (view raw or whole thread) Thread: 2008-09-10 23:11:13 from "Ruben Gouveia" 2008-09-10 23:19:58 from Stephan Szabo 2008-09-10 23:59:49 from "Richard Broersma" 2008-09-11 12:03:23 from Tom Lane 2008-09-11 12:43:04 from "Igor Neyman" Lists: pgsql-sql I tried to do the following and got the following error message: select employee,count(distinct tasks) from job where greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1' group by employee; ERROR: aggregates not allowed in WHERE clause ********** Error ********** ERROR: aggregates not allowed in WHERE clause SQL state: 42803 Is there away around this? Should i create a function to populate a variable that can be used in it's place...will that even work? Responses Re: Aggregates in WHERE clause? at 2008-09-10 23:19:58 from Stephan Szabo Re: Aggregates in WHERE clause? at 2008-09-10 23:59:49 from Richard Broersma Re: Aggregates in WHERE clause? at 2008-09-11 12:43:04 from Igor Neyman pgsql-sql by date Next:From: Stephan SzaboDate: 2008-09-10 23:19:58 Subject: Re: Aggregates in WHERE clause? Previous:From: Emi LuDate: 2008-09-10 21:13:45 Subject: pg_restore in java connection (auto_commit = false) Privacy Policy | About PostgreSQL Copyright © 1996-2016 The PostgreSQL Global Development Group