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.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2023-05-14 23:39:59 +0000
Seen: 10 times
Last updated: May 14 '23
How can the SQL debug mode be activated in PostgreSQL version 15.2?
What is the procedure for using pg_restore on Windows with Docker?
What is the equivalent of SQL Server NVARCHAR in PostgreSQL?
What is the method to retrieve the JSON data from a column in SQL?
How to solve the issue of being unable to establish a connection with libpq on Mac M1?
Is it possible to launch multiple instances of PostgreSQL using Spring Boot Testcontainers?
Due to SyntaxError, why am I unable to create a TIMESTAMP WITH TIMEZONE column in postgres?