Revision history [back]

To obtain the total count instead of individual count from a column utilizing Having clause on another column while utilizing select subqueries in PostgreSQL, you can use the following query:

SELECT count(*) 
FROM (
    SELECT column1, count(column2) 
    FROM table 
    GROUP BY column1 
    HAVING count(column2) > ( 
        SELECT avg(cnt) 
        FROM ( 
            SELECT count(column2) as cnt 
            FROM table 
            GROUP BY column1 
        ) as counts 
    )
) as subquery;

In the above query, we are using a subquery to first calculate the average count of column2 for each value of column1. Then, we use this average count in our main query's Having clause to filter out only those rows where the count of column2 is greater than this average value.

Finally, we wrap the entire subquery in another SELECT statement to count the total number of rows returned.