Скачать или смотреть How to Calculate the Average of Fields in MySQL Excluding Zero Values

Learn how to exclude zero values in MySQL average calculations with a simple and effective SQL query solution.
---
This video is based on the question https://stackoverflow.com/q/62495286/ asked by the user 'Rob van den Eijnden' ( https://stackoverflow.com/u/13709375/ ) and on the answer https://stackoverflow.com/a/62495475/ provided by the user 'Rob van den Eijnden' ( https://stackoverflow.com/u/13709375/ ) 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: How can i calculate the average of fields and exclude the 0 values

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 Calculate the Average of Fields in MySQL Excluding Zero Values

When working with databases, especially when analyzing ratings or reviews, you might encounter situations where certain entries are flagged as 'does not apply' which translates to a value of zero. This can distort the averages you want to calculate, making them inaccurate. Today, we'll tackle the issue of calculating averages in MySQL while excluding these zero values.

The Problem at Hand

Let’s take a scenario where you have a table named reviews containing various customer ratings for coffee products. The table includes a column for each product type—coffee, cappuccino, espresso, and machine.

Table Sample:

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

In this example, Mike’s zero ratings should be excluded from the average calculation. The current query without exclusions provides the following averages:

Coffee: 2.67

Cappuccino: 3

Espresso: 2.33

Machine: 4.67

However, the required averages—considering that Mike doesn’t drink coffee—should be:

Coffee: 4

Cappuccino: 4.5

Espresso: 3.5

Machine: 4.67

So how do we achieve that?

The Solution: Using NULLIF

The solution lies in using the NULLIF function within your SQL query. This function allows you to transform zero values into NULL, which are then ignored in aggregate functions such as AVG.

Implementation Steps:

Here's how you can modify your original SQL query:

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

Breakdown of the Query:

NULLIF(coffee, 0): Converts zero values of coffee ratings into NULL. Thus, when calculating the average, only non-zero values will be considered.

ROUND(AVG(...)): Calculates the average and rounds off to maintain a cleaner output.

Expected Results

With the updated SQL query, running it on the previously mentioned data will yield the expected averages without the influence of Mike's zeros:

Coffee: 4

Cappuccino: 4.5

Espresso: 3.5

Machine: 4.67

Conclusion

In summary, calculating averages while excluding zero values in MySQL is straightforward with the NULLIF function. This makes your data analysis far more accurate and meaningful, particularly in scenarios where non-applicable values exist. Don’t let the zeros skew your insights—implement this technique today!

If you have further questions or need assistance with your SQL queries, feel free to ask!