Today I’ll show you the most essential SQL functions, that you will use for finding the maximums or the minimums (MAX, MIN) in a data set and to calculate aggregates (SUM, AVG, COUNT). Then I’ll show you some intermediate SQL clauses (ORDER BY, GROUP BY, DISTINCT) as well, that you have to know to be able to use SQL for data analysis efficiently!

And this is gonna be super exciting, as we will still use our 7M+ lines data set!

Before we start…

… I recommend to go through these articles first – if you haven’t done yet:

  1. Setup your own data server to practice: How to setup Python, SQL, R and Bash (for non-devs)
  2. Install SQL Workbench to manage your SQL stuff better: How to install SQL Workbench for postgreSQL
  3. Read the first two episodes of the SQL for Data Analysis series ep1 and ep2
  4. Make sure, that you have the flight delays data set imported – and if you don’t, check this video.

SQL functions to aggregate data

Okay, let’s open SQL Workbench and connect to your data server (LINK)!

Can you recall our base query?
It was:

SELECT *
FROM flight_delays
LIMIT 10;

And it returned the first 10 lines of this huge data set.

SQL base query

We are going to modify this query to get 5 interesting numbers:

  • how many flights are exactly in our table
  • the summary of the airtimes (note: airtime is practically the flight time) of these flights
  • the average arrival delays and the average departure delays
  • the maximum distance, that any of these flights made
  • the minimum distance, that any of these flights made

All these are going to be very easy, I promise… But make sure, you are doing the coding part with me, because 100% understanding of these basic stuff will be important on the long term.

SQL COUNT function to count lines

The easiest aggregation is to count lines in your table and this is why the COUNT function is for. The only thing you have to change – compared to the above base query – is what you SELECT from your table. Remember? It can be everything (*), or it can be specific columns (arrdelay, depdelay, etc) – and now let’s expand this list with functions. Copy this query into SQL Workbench and run it:

SELECT COUNT(*)
FROM flight_delays
LIMIT 10;

SQL COUNT function

Result is: 7275288.
The function itself is the COUNT and it says that count the lines using every column (*)… You can change the * to any columns’ name (eg. arrdelay) – and you will get the very same number. Try this:

SELECT COUNT(arrdelay)
FROM flight_delays
LIMIT 10;

SQL COUNT function column

Right? Same result: 7275288.

Note 1: It’s only true, when you don’t have NULL values (empty cells) in your table (we don’t have NULL values in the flight_delays data set at all). I’ll get back to the importance of NULL later.
Note 2: in fact you won’t need the LIMIT clause in this SQL query, as you will have only one line of data on your screen. But I figured, that sometimes it might be better to keep it there, so even if you mistype anything, your SQL Workbench won’t freeze by accidentally trying to return 7M+ lines of data.

SQL SUM function to get summary

Now we want to get the airtime for all flights – added up. With other words: make a summary on column “airtime”. The SUM function works with the same logic as COUNT does. The only exception, that in this case you have to specify the column (in this case “airtime”). Try this:

SELECT SUM(airtime)
FROM flight_delays;

The total airtime is a massive 748015545 minutes.

SQL SUM function

SQL AVG function to get the mean

Our next challenge is to find the mean of the arrival delays and the mean of the departure delays… The function for that is AVG (refers to “average”) – functioning with the exact same logic as SUM and COUNT.

SELECT AVG(depdelay)
FROM flight_delays;

SQL AVG function depdelay

Result: 11.36

SELECT AVG(arrdelay)
FROM flight_delays;

Result: 10.19

Cool!

SQL MAX and MIN functions to get maximums and minimums

And finally let’s find the maximum and the minimum values of a given column. Finding the maximum and minimum distances for these flights sounds interesting enough… MIN and MAX operate just like SUM, AVG and COUNT did:

SELECT MIN(distance)
FROM flight_delays;

Result: 11 miles. Well, maybe take the bike next time…

SQL MIN function

SELECT MAX(distance)
FROM flight_delays;

Result: 4962

SQL MAX function

Okay! That was it – these are the basic SQL functions you have to know… It’s time to tweak them a little bit.

Basic segmentation analysis with SQL – aka. the GROUP BY clause

As a Data Analyst or Scientist you will do segmentations most probably all the time. For instance it’s interesting to know, what is the average departure delay of all flights (we have just learned, that it’s 11.36). But when it comes to business decisions, this number is not actionable at all. However if we turn this information into a little bit more useful format – let’s say we break it down by airports – it will become instantly something, we can act on!

Here’s a simplified chart about how SQL does automatic segmentation based on column values:

SQL GROUP BY clause

The process has three important steps:

STEP 1 – Specify, which columns you want to work with as an input. In our case we want to use the list of the airports (origin column) and the departure delays (depdelay column).

STEP 2 – Specify, which column(s) we want to create our segmentation on. For us it’s the origin. SQL automatically looks for every unique value in this column (on the above example – airport 1, airport 2 and airport 3), then creates groups from them and sort each line from your data table into the right group.

STEP 3 – Finally it calculates the averages using the SQL AVG function for each group and returns the results on your screen.

The only new thing here is the “grouping” at STEP 2. We have an SQL clause for that. It’s called GROUP BY. Let’s see it in action:

SELECT
  AVG(depdelay),
  origin
FROM flight_delays
GROUP BY origin;

SQL GROUP BY example

If you scroll through the results, you will see, that there are some airports with above 30-40 minutes departure delay. From a business perspective it’s important to understand, that what’s going on at those airports. On the other hand it’s also worth to take a closer look, that how the good airports (depdelay close to 0) are managing to reach this ideal phase. (Yeah, it’s over-simplified, but just for example…)

But what did just happen SQL-wise? We have selected two columns – origin and arrdelay. “origin” has been used to create the segments (GROUP BY origin). “arrdelay” has been used to calculate the averages of the arrival delays in these segments (AVG(arrdelay)).

Note: As you can see, the logic of SQL is not as linear as it was in bash. If you write an SQL query, the first line of it could highly rely on the last line. When you’ll write really long and complex queries, this might cause some unexpected errors and thus of course a little headache too… But that’s why I find very-very important to give yourself enough time to practice the basic things and make sure that you fully understand the relation of the different clauses, functions and other stuff in SQL.

Test yourself #1

Here’s a little assignment to practice on and to double-check if you understand everything so far! The task is:
Print the total monthly airtime!
.
.
.
Ready?
Here’s my solution:

SELECT
  month,
  SUM(airtime)
FROM flight_delays
GROUP BY month;

SQL test yourself 1

I did pretty much the same stuff, that I have done before, but now I created the groups based on the months – and this time I had to use the SUM function.

Test yourself #2

And another exercise:
Calculate the average departure delay by airports again, but this time take only those flights, that flew more than 2000 miles (you will find this info in the distance column).
.
.
.
Here’s the query:

SELECT
  AVG(depdelay),
  origin
FROM flight_delays
WHERE distance > 2000
GROUP BY origin;

SQL test yourself 2

The takeaway from this assignment is something, that you might have already realized: you can use the SQL WHERE clause to filter on those columns too, that are not part of your SELECT statement.

SQL ORDER BY to sort by column(s)

Let’s say, we want to see, which one was the busiest airport in 2007. You can get the number of departures by airport really easily using COUNT function with GROUP BY clause:

SELECT
  COUNT(*),
  origin
FROM flight_delays
GROUP BY origin;

SELECT without order

But this list is not sorted by default… To have that too, you need to add only one more SQL clause: ORDER BY. When you use it, you always have to specify that by which column you want to order… It’s pretty straightforward.

SELECT
  COUNT(*),
  origin
FROM flight_delays
GROUP BY origin
ORDER BY count;

Note: the column you will get after the COUNT function will be a new column… And it has to have a name – so SQL automatically names it “count” (check the latest screenshot above). When you refer to this column in your ORDER BY clause, you have to use this new name. I’ll get back to this in my next article in details. If you find it weird, let’s try the same query but with ORDER BY origin – and you will understand it instantly.

SQL ORDER BY clause

Hm, almost there. But the problem, that the less busy airport is on the top – in other words, we got a list in ascending order. That’s the default for ORDER BY (in our postgreSQL database at least). But you can change this to descending order by simply adding the DESC keyword after it!

SELECT
COUNT(*),
origin
FROM flight_delays
GROUP BY origin
ORDER BY count DESC;

SQL ORDER BY clause with DESC

Great!

DISTINCT to get unique values only

This is the last new thing for today. And this will be a very simple one. If you are curious, how many different airports are in your table:

a) you can find it out with the GROUP BY clause. (Can you find it out, how? :-))
b) you can find it out even more easily by using DISTINCT

DISTINCT basically removes all duplicates. Try this:

SELECT DISTINCT(origin)
FROM flight_delays;

Now you have only unique airports!

SQL DISTINCT clause

By the way the GROUP BY version would look like this:

SELECT origin
FROM flight_delays
GROUP BY origin;

Though result-wise it’s pretty much the same, the preferred way to do this is to use the DISTINCT – on more complex queries it will help you to keep your query simpler!

Test yourself #3

You have learnt today a tons of small, but useful stuff. I’ll give you one more assignment, that will summarize pretty much everything – even the previous two articles (ep1 and ep2). This is going to be a difficult one, but you can do it! If it doesn’t work for the first, try to break it down into smaller tasks, then build and test your query, till you get the result.
The task is:

List the:

  • top 5 plane (identified by the tailnum)
  • by the number of landings
  • take only the plans, that landed on PHX or on SEA airport
  • on Sundays

(eg. if the plane with the tailnumber ‘N387SW’ landed 3 times in PHX and 2 times in SEA in 2007 on any Sundays, then it’s 5 in total. And we need the top 5 planes, where this number is the greatest.) Ready? Set! Go!
.
.
.
Done? Here’s my solution:

SELECT
  COUNT(*),
  tailnum
FROM flight_delays
WHERE dayofweek = 7
  AND dest IN ('PHX', 'SEA')
GROUP BY tailnum
ORDER BY count DESC
LIMIT 5;

SQL test yourself 3

And with comments:
SELECT –» select…
COUNT(*), –» this function count the number of lines in a given group, to do that it needs the group by clause later
tailnum –» this will help to specify the groups (referred in the GROUP BY function later)
FROM flight_delays –» the name of the table
WHERE dayofweek = 7 –» a filter for Sundays only
AND dest IN ('PHX', 'SEA') –» filter for PHX and SEA destinations only
GROUP BY tailnum –» this is the clause that helps us to put the lines into different groups
ORDER BY count DESC –» and let’s order by the number of lines in a given group
LIMIT 5; –» list only the top 5 elements

Conclusion

And that’s it! You have learned a lot again today – SQL functions (MIN, MAX, AVG, COUNT, SUM) and new important SQL clauses (DISTINCT, ORDER BY, GROUP BY).
If you managed to get the last exercise done by yourself, I can tell you, that you have a really good basic knowledge about SQL! Congrats! If not, don’t worry, just make sure, that you re-read these first 3 chapters (ep1, ep2, ep3), before you continue with episode 4!
To help you practice more I’ll provide some videos in the future to do that, on my Youtube channel: Data Science for Beginners!

If you want to be notified about my new articles, videos, webinars and other cool data stuff, subscribe to my weekly Newsletter (0% spam, 100% useful data content).

Cheers,
Tomi Mester