Calculating Summary Statistics in SQL
When you first get your hands on a data set, what's it like:
- quickly get a feel for the data?
- are there outliers?
- is the data shaped abnormally?
These are questions you might have about your data. The disadvantage of a spreadsheet-like interface is that it's difficult to understand your data, speically when you're seeing one for the first time.
For this purpose, we have summary statistics. Fortunately, SQL has a robust set of functions to do exactly that.
As an example, we'll use a list of the fastest growing companies in the United States according to Inc. magazine. The data table is formatted as:
|Fuhu||California||Consumer Products & Services||227||195640000|
|Quest Nutrition||California||Food & Beverage||191||82640563|
|Reliant Asset Management||Virginia||Business Products & Services||145||85076502|
|Vacasa||Oregon||Travel & Hospitality||264||26263454|
|Go Energies||North Carolina||Energy||11||32851754|
|Minute Key||Colorado||Consumer Products & Services||113||15782039|
|Sainstore||Kansas||Business Products & Services||135||19243863|
|The HCI Group||Florida||Health||153||34583142|
|Dynamic Dental Partners Group||Florida||Health||313||19802935|
Inspired by Spreadsheets
Our inspiration comes from spreadsheet applications like Microsoft Excel or Google Sheets that show you a summary of a column in the footer:
Understanding the Data
For the purposes of this example, we are only interested in the distribution of the numerical columns:
We will calculate the mean and measure the level of variability (min and max) of the data. These are often a good start.
SELECT 'Total', sum(employees) AS employees, sum(revenue) AS Revenue FROM inc5000 UNION SELECT 'Average', avg(employees), avg(revenue) FROM inc5000 UNION SELECT 'Min', min(employees), min(revenue) FROM inc5000 UNION SELECT 'Max', max(employees), max(revenue) FROM inc5000
Will give us a result like this:
This is cool, but let's append it to the main table above so that we have everything to look at in one place.
SELECT name, employees, revenue FROM inc5000 UNION SELECT 'Total', sum(employees) AS employees, sum(revenue) AS Revenue FROM inc5000 UNION SELECT 'Average', avg(employees), avg(revenue) FROM inc5000 UNION SELECT 'Min', min(employees), min(revenue) FROM inc5000 UNION SELECT 'Max', max(employees), max(revenue) FROM inc5000
Unfortunately, it seems like the summary statistics are lost within the rows of the main table. We should bring up to the top so that it's easy to glance at. This is possible by artifically creating a column with the row index we want and sorting by them.
SELECT * FROM ( SELECT 5, company, employees, revenue FROM inc5000 UNION SELECT 1, 'Total', sum(employees) AS employees, sum(revenue) AS Revenue FROM inc5000 UNION SELECT 2, 'Avg', avg(employees), avg(revenue) FROM inc5000 UNION SELECT 3, 'Min', min(employees), min(revenue) FROM inc5000 UNION SELECT 4, 'Max', max(employees), max(revenue) FROM inc5000) stats ORDER BY 1
More summary statistics
The SQL in this post are fairly simple using built-in functions. We'll cover more advanced summary statistics in other sections:
You are reading a series of Advanced SQL tricks.
Read this next: Calculating Summaries with Histogram Frequency Distributions
Join 300+ data analysts who are using our weekly SQL tips to improve their skills. No spam, promise!