Home » Tutorials » SQL » Functions

Functions

And so the DATETIMEs came to pass, and the light shined through the best part of SQL, functions. There are quite a bit of functions, and you might actually use the majority of them. I will just cover a basic overview of how to use a few functions, so you can easily use the rest when your tinkering around in SQL. Count, sum, and average are possibly the most common and in that order. However, sometimes you will use super helpful functions like max and min. Let’s discover what they look like, then we’ll use them.

Common SQL Functions

  • count() – counts the number of records returned from the query
  • sum() – adds the values in records returned from the query together
  • avg() – averages values in records returned from the query
  • max() – finds the highest value in all of the records returned from the query
  • min() – finds the lowest value in all of the records returned from the query

Our “Users” Table:

idnameviews
1Hillary100
2Al150
3George300
Example
SELECT COUNT(*) 
FROM Users
Result 3

As you can see, all COUNT() does is count the values in the column specified inside the parentheses. However, we used the * wildcard, so it just counted all records.

Example
SELECT SUM(views) 
FROM Users
WHERE id <= 2 
Result 250

Now, we can use the SQL Functions on columns. We said give us the sum, sum(), of our views column, but only where the column id is less than or equal to 2. The 250 is the sum of Hillary and Al’s views because their id is less than or equal to 2. Note: math functions in SQL won’t do so well with strings.

Example
SELECT AVG(views) 
FROM Users
WHERE id >= 2 
Result 225

The SQL Function average, avg(), tells SQL to crunch our numbers in the views column and give us the average, but only where the id is greater than or equal to 2. This only averages George and Al’s views, which gives us 225.

Example
SELECT MAX(views)
FROM Users
Result 300

So, the SQL Function max, obviously short for maximum, returns 300 because it is the highest number in our views column.

Example
SELECT MIN(views)
FROM Users
Result 100

Exactly on the opposite spectrum of is the MIN(), short for minimum, SQL Function. This function grabs the lowest value of our views column.

And that’s all folks. Feel free to play around with the other SQL functions



Link/cite this page

If you use any of the content on this page in your own work, please use the code below to cite this page as the source of the content.

  • Stewart, Suzy. "Functions". After Hours Programming. Accessed on March 16, 2024. https://www.afterhoursprogramming.com/tutorial/sql/functions-sql/.

  • Stewart, Suzy. "Functions". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/sql/functions-sql/. Accessed 16 March, 2024.

  • Stewart, Suzy. Functions. After Hours Programming. Retrieved from https://www.afterhoursprogramming.com/tutorial/sql/functions-sql/.



Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.