Скачать или смотреть Solving SQL Queries with SUM() and COUNT() Functions in PostgreSQL

Learn how to effectively use `SUM()` and `COUNT()` functions in PostgreSQL to aggregate data based on specific criteria. This guide provides clear examples and solutions to common mistakes.
---
This video is based on the question https://stackoverflow.com/q/63265600/ asked by the user 'user6688' ( https://stackoverflow.com/u/7286392/ ) and on the answer https://stackoverflow.com/a/63265633/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) 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: SQL query using Sum() and count() functions

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.
---
Mastering SQL Queries with SUM() and COUNT() in PostgreSQL

When working with SQL, especially as a beginner, it can be challenging to aggregate data correctly. One common task is to calculate totals using the SUM() and COUNT() functions. Today, we’ll explore a specific case involving these functions in PostgreSQL, focusing on how to aggregate different types of rows effectively.

The Problem

Imagine you have a table named exer that stores different types of exercises classified as ROOT, DYNAMIC, and TEST. You want to find the total number of exercises of each type. A rookie mistake can lead to syntax errors or incorrect aggregations. Here are two attempts made by a beginner that highlight the common pitfalls:

Using SUM() incorrectly:

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

Using COUNT() also incorrectly:

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

Both attempts fail to yield the expected results due to syntax issues and incorrect usage of SQL functions. But fear not, there is a solution!

The Solution

To aggregate data correctly and efficiently, we can use the FILTER clause, which PostgreSQL supports. This approach allows us to apply conditions to our aggregate functions without complicating the syntax.

Using the FILTER Syntax

Here’s how you can rewrite your SQL query to utilize the FILTER syntax correctly:

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

Explanation:

The FILTER clause allows you to specify conditions straight within the COUNT() function.

This makes the query cleaner and avoids the need for a GROUP BY clause since each exercise type is counted separately.

Alternative Method without FILTER

If for some reason you want or need to avoid the FILTER syntax, you can achieve the same result using the SUM() function with a CASE statement as follows:

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

Explanation:

Here, you are summing up conditional statements which return 1 for each match and 0 otherwise. This provides the same total counts.

Final Thoughts

With the right syntax, you can easily aggregate data in PostgreSQL using SUM() and COUNT() functions. Remember to use the FILTER clause for a cleaner solution or the CASE statement for a traditional approach. By practicing these examples, you'll build up your SQL skills and enjoy querying data more efficiently!

Happy querying!