Скачать или смотреть How to Count Distinct Values Across Multiple Columns in SQL

Learn how to efficiently count distinct values for multiple food items ordered by each person in SQL, ensuring clarity and simplicity in your database queries.
---
This video is based on the question https://stackoverflow.com/q/69681908/ asked by the user 'morganherg' ( https://stackoverflow.com/u/17205962/ ) and on the answer https://stackoverflow.com/a/69681994/ provided by the user 'forpas' ( https://stackoverflow.com/u/10498828/ ) 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: Select count of multiple columns WHERE another column is distinct

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.
---
Understanding the Problem: Counting Distinct Values in SQL

If you're working with SQL and need to analyze how many times different food items were ordered by various individuals, you might find yourself facing a common challenge. Specifically, you want to count how many unique orders exist for each type of food without redundancy in your results. Your dataset might look something like this:

PersonIdHamburgerFriesSoda1Unique burgerUnique FryUnique Soda2Unique burgerUnique FryUnique Soda1Unique burgerNULLUnique Soda3Unique burgerUnique FryNULLIn this table, you can see that a PersonId can appear multiple times, each time potentially with different combinations of food items ordered. Your goal is to generate a summary that counts how many distinct hamburgers, fries, and sodas were ordered, ensuring that each person is only counted once for each food item they ordered.

The Solution: Conditional Aggregation

To achieve the desired results, you can utilize SQL's conditional aggregation feature. This allows you to conditionally count rows based on whether they meet a specified criterion—in this case, whether each food item is not null for unique PersonIds.

Here’s how to structure your SQL query

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

Breaking Down the Query

COUNT(DISTINCT CASE WHEN ... THEN ... END): This construct allows you to count distinct PersonIds for each food item only if the value is not null.

AS HamburgerCount, FriesCount, SodaCount: These aliases give your output columns meaningful names, making it easy to understand what they represent.

Step-by-Step Explanation

Select Statement: Start your query with a SELECT, which allows you to define what you want to retrieve.

COUNT with DISTINCT: This part counts unique occurrences. The DISTINCT keyword ensures that you do not double-count any PersonId for the same item.

CASE WHEN Structure: This conditions your count, so it only counts the PersonId when the Hamburger, Fries, or Soda column is not null, allowing you to effectively filter out rows without meaningful data.

Table Reference: At the end, replace your_table_name with the actual name of your table from which you're querying the data.

Expected Result

With the SQL query structured this way, your result set will yield:

HamburgerCountFriesCountSodaCount322This output indicates that there are three unique Hamburgers ordered by different people, two unique Fries, and two unique Sodas.

Conclusion

By employing conditional aggregation in your SQL queries, you can efficiently count distinct entries across multiple columns while maintaining the uniqueness required. This method not only simplifies your SQL commands but also enhances the performance of your queries, making them more efficient and easier to read.

Feel free to adapt this query to your specific database structure and data. With this understanding, you're now ready to tackle your counting challenges in SQL!