FILTER

Filtering on aggregated data

intro

Performing data analysis in SQL with proper tools and code.

Tools used in the tutorial
Tool Description Link
DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Key Takeaways

  • In SQL we can filter on values in the original, non-aggregated data as well as values generated from an aggregate function applied over values of another column.
  • To filter on values from aggregate functions grouped by values of another column, however, we need to use slightly different code.

Introduction

If you have ever written any piece of code to analyze data you probably encountered a situation where the output on the screen did not match what you wanted. If you are like me, you also may have said (or yelled) to the computer, ”Why can’t you just do what I want you to do?!” followed by a moment of realization that the code you wrote was not completely correct for completing the intended task at hand. For all the great things they can do, computers do not infer meaning and intent from people well. As such, computers require very deliberate instructions, written in code, to produce the desired result.

SQL is no different, and one area that is prone to a disconnect between intention and output involves filtering on aggregated data. In a previous post entitled ”Translating Basic Excel to SQL” I covered some fundamental operations in SQL, including WHERE statements, which are used to filter rows based on some condition (the materials used in that prior article are also used in this article and can be downloaded here). For example, the media below shows the execution of a WHERE statement in DbVisualizer.

Execution of a WHERE statement
Execution of a WHERE statement

In ”Translating Basic Excel to SQL” I also discussed the use of aggregate functions and GROUP BY statements. Using publicly available Airbnb data in New York City (original data source can be found here) say, for example, we wanted to see the mean price per listing by neighborhood group. We would use the code below to accomplish this task.

        
1 SELECT neighbourhood_group, AVG(price) 2 FROM AB_NYC_2019 3 GROUP BY neighbourhood_group;

Here is the execution of the code above in DbVisualizer.

execution of the code

Filtering Values From Aggregate Functions

Now, say that we want to identify neighbourhood groups and associated mean listing prices for those neighbourhood groups where the mean listing price is less than $100.00. A first, and reasonable, guess on obtaining the desired output would be to use WHERE to filter.

        
1 SELECT neighbourhood_group, AVG(price) FROM AB_NYC_2019 2 GROUP BY neighbourhood_group 3 WHERE AVG(price) < 100.00;

Let’s see what happens when we execute this code.

error message

We get an error message and the code in the editor is underlined with wavy red lines, which are present to help draw attention to erroneous code. So why is this code erroneous? Well, WHERE applies to non-aggregated records, but we want to filter mean price values, which are aggregated across neighbourhood groups. Now you are probably wondering if there is a way to filter aggregated values and the answer is yes. Even better, the answer is simple! Just replace WHERE with HAVING.

        
1 SELECT neighbourhood_group, AVG(price) FROM AB_NYC_2019 2 GROUP BY neighbourhood_group 3 HAVING AVG(price) < 100.00;

Now the code is properly executed.

Conclusion

Being able to filter data is a fundamental skill for data analyses. When filtering in SQL, it is important to be mindful of where values in the filtering logic originated. That is, do the values on which we want to apply a filter represent the most granular level of detail in the particular data table of interest, or do the values represent a higher level of aggregation produced by an aggregate function and GROUP BY statement? Knowing this distinction and knowing the proper tools to use in both cases, that is, WHERE vs HAVING, can save a lot of headaches when performing data analyses in SQL.

Dbvis download link img
About the author
Scott A. Adams

Scott is a data scientist, social scientist, and educator who cannot think of anything witty to put here.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Inner Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 10 MINS 2023-11-30
title

Leveraging Snowflake's Time Travel

author Ochuko Onojakpor tags SNOWFLAKE TIME TRAVEL 11 MINS 2023-11-28
title

MariaDB vs MySQL: The Ultimate Comparison

author Antonello Zanini tags MARIADB MySQL 7 MINS 2023-11-23
title

Change Data Capture: A Comprehensive Guide

author Ochuko Onojakpor tags DATA CAPTURE 14 MINS 2023-11-21
title

SQL vs NoSQL Databases: Which is Better?

author Ochuko Onojakpor tags NOSQL SQL 16 MINS 2023-11-20
title

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16
title

SQL Cheat Sheet: A Comprehensive Guide to SQL Commands and Queries

author Bonnie tags CHEAT SHEET SQL 18 MINS 2023-11-15
title

Error Establishing a database connection - Common Reasons and Solutions

author Ochuko Onojakpor tags CONNECTION ERROR 12 MINS 2023-11-13
title

How to work with SQL query optimization

author Antonello Zanini tags PERFORMANCE 10 MINS 2023-11-09

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗