Aggregates not allowed in WHERE clause in postgreSQL error

Question

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 < 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!

You want the `having` clause: http://www.postgresql.org/docs/current/static/tutorial-agg.html — , 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](http://stackoverflow.com/questions/8684486/rails-3-getting-a-pgerror-error-aggregates-not-allowed-in-where-clause-on-a-a) — Taryn, Jan 30 '14 at 19:58
Possible duplicate of [How to avoid error "aggregate functions are not allowed in WHERE"](http://stackoverflow.com/questions/20991729/how-to-avoid-error-aggregate-functions-are-not-allowed-in-where) — Aziz Shaikh, Jan 20 '17 at 10:02

Gordon Linoff · Accepted Answer · 2014-01-30T20:09:55.593

2

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;

edited Jan 30 '14 at 20:09

answered Jan 30 '14 at 19:59

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon Linoff, I did exactly and it gave me the following syntax error: `ERROR: syntax error at or near ";" LINE 10: GROUP BY a.hash, a.package, a.version_name; ^ ********** Error ********** ERROR: syntax error at or near ";" SQL state: 42601 Character: 446` I have pasted exact query :( Do I need to add anything else? – AKIWEB Jan 30 '14 at 20:06
@akiiddweeber . . . The closing paren on the subquery is now where it should be. – Gordon Linoff Jan 30 '14 at 20:10
Thank you Gordon, what does "t" stands for is it like assigning a whole function with some name? and I ran the same now getting different error: `ERROR: missing FROM-clause entry for table "a" LINE 10: WHERE a.total < 8 * avgtotal ^ ********** Error ********** ERROR: missing FROM-clause entry for table "a" SQL state: 42P01 Character: 386` – AKIWEB Jan 30 '14 at 20:14
1

@akiiddweeber . . . The `t` is called a table alias. It names a subquery or table for use in other parts of the query. The code you are referring to has changed. – Gordon Linoff Jan 30 '14 at 20:16

Aggregates not allowed in WHERE clause in postgreSQL error

1 Answers1

Linked