Aggregates not allowed in WHERE clause in postgreSQL error

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!

Answers


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;

Need Your Help

Array of threads, only a few active at a time?

c# winforms multithreading .net-4.0 threadpool

Apologies if this is simple question, but Im extremely worn down and though thinking straight. I recently set up threading and its working great. The user selects items to have work performed on,...

Maven2 multi-module ejb 3.1 project - deployment error

maven-2 java-ee java-ee-6 ejb-3.1 glassfish-3

The problem is taht I get the following error qhile deploying my project to Glassfish: