Filtering on
aggregated data

Performing data analysis in SQL with proper tools and code

Scott A Adams
Article written by: Scott A. Adams
Reading: 7 min

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

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.

Filtering aggregated data

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.

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.

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

Here is the execution of the code above in DbVisualizer.

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.

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

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

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.

SELECT neighbourhood_group, AVG(price) FROM AB_NYC_2019
GROUP BY neighbourhood_group
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.

If you are interested in reading about related topics please find more articles on our website

Related articles

Excel to SQL Basics

If you know Excel you can easily start learning SQL.

Read more

How to join your tables using ERD

Why can't we just have a single table? Navigating relationships in relational databases.

Read more

Create your own SQLite database

This article will teach you how to set up your own relational database using DbVisualizer and SQLite.

Read more