Functions

22.08.2016 |

Episode #8 of the course Intro to SQL by Mike Schmitt

 

SQL provides a number of built-in functions that allow you to perform calculations on your data set. Let’s take a look at these and use our fitness tracker app as an example. Available to us is a table named StepLog, which holds the number of steps that we take each day:

IntrotoSQL_8.1

What is displayed in your app, however, is much more helpful, as we are given averages and the total number of steps, among other data points. These values can be calculated rather easily with built-in SQL functions, and here are a few available for use:

COUNT(column_name) – This returns the number of rows.
AVG(column_name) – This returns the average value of the passed column.
MAX(column_name) – This returns the largest value in the passed column.
MIN(column_name) – This returns the smallest value in the passed column.
SUM(column_name) – This returns the sum of the passed column.

Let’s calculate the sum and average of our steps taken:

SELECT SUM(StepsTaken), AVG(StepsTaken) FROM StepLog;

Our SQL statement is similar to a basic SELECT, but we now have the function calls on the columns we wish to perform the calculations on (the sum of column StepsTaken and the average of column StepsTaken). Our result set when we execute the query shows as follows:

IntrotoSQL_8.2

We are shown that the sum of steps taken is 64,918 and the average is roughly 9,274, but the column headers aren’t too pretty. We can rename those with what is called an alias to make them more descriptive.

SELECT SUM(StepsTaken) AS “Total Steps Taken”
 , AVG(StepsTaken) AS “Average Steps Taken”
 FROM StepLog;

The additions to the SQL syntax are the AS, which signals we will be renaming the column header, followed by our renaming in quotes. The quotes allow us to use spaces in the header name. Our result set will now appear like so:

IntrotoSQL_8.3

This looks much cleaner!

And now we have the ability to gather some additional insight into our data with basic calculations by way of functions!

Explore on your own with an SQL Fiddle of today’s lesson!

 

Recommended book:

“Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design” by Michael J. Hernandez

 

Share with friends