1. SQL Recipes
2. Summarizing Data
3. Calculating Summary Statistics

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:

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:

• employees
• revenue

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

Final Result

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:

• medians
• variances, standard deviations and z-scores
• correlations
• linear regression line