intro
Performing data analysis in SQL with proper tools and code.
Key Takeaways
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.
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.
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.
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.
