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.
Asked: 2023-05-14 23:39:59 +0000
Seen: 10 times
Last updated: May 14 '23