Скачать или смотреть How to Count Rows with Group By and Having Count in PostgreSQL

Discover how to accurately count rows in PostgreSQL using `GROUP BY` and `HAVING COUNT` clauses with practical examples and solutions.
---
This video is based on the question https://stackoverflow.com/q/63798671/ asked by the user 'devilchan' ( https://stackoverflow.com/u/13459480/ ) and on the answer https://stackoverflow.com/a/63799327/ provided by the user 'Akhilesh Mishra' ( https://stackoverflow.com/u/7772483/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: count rows with group by and having count (postgres)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Count Rows with Group By and Having Count in PostgreSQL

When working with databases, a common requirement is to count rows based on specific conditions. In PostgreSQL, achieving this often involves the use of GROUP BY and HAVING clauses. However, for users unfamiliar with these functions, it can be a bit challenging. Let’s delve into a frequent issue that arises when trying to perform these operations and explore how to resolve it effectively.

The Problem

Imagine you have a table named imgs_tags that contains the following data:

[[See Video to Reveal this Text or Code Snippet]]

You want to count how many distinct img_ids have exactly two tags (in this case, tags 2 and 3). To achieve this, you might write a query like the following:

[[See Video to Reveal this Text or Code Snippet]]

However, this query returns three rows, each with a count of 2, instead of a single count of 3. Why does this happen? Let's break down the solution.

Understanding the Solution

The reason your original query didn't work as expected is that you're counting the number of img_ids for each group, rather than aggregating those results into a single total. To resolve this, we can utilize a subquery or a Common Table Expression (CTE). Here’s how to do it step-by-step.

Method 1: Using a Subquery

You can wrap your original query in a subquery like this:

[[See Video to Reveal this Text or Code Snippet]]

In this query, the inner query selects distinct img_ids that meet your criteria.

The outer query counts how many rows this inner query returns, giving you the correct total count of 3.

Method 2: Using Common Table Expression (CTE)

Another way is to use a Common Table Expression (CTE), which can make your queries cleaner and more readable, especially if you plan to perform further operations with the data:

[[See Video to Reveal this Text or Code Snippet]]

The CTE defines a temporary result set that you can reference within your main query.

This method also yields a count of 3, but with an organized structure that might be more understandable.

Conclusion

Counting rows with specific conditions in PostgreSQL using the GROUP BY and HAVING clauses doesn't have to be a daunting task. By understanding the underlying logic and utilizing techniques like subqueries or CTEs, you can efficiently get the results you need. Remember, your ultimate goal is to ensure that the outer query appropriately aggregates the data from your inner query to reflect the total counts accurately.

In summary, if you find yourself counting rows but still getting unexpected results, consider refining your approach with one of the methods discussed above.