SQL has many built-in useful functions that will help perform calculations on data within your database. This will help organize and optimize your workflow when working within a SQL database. All the functions can be broken down into two different categories: Aggregate Functions, and Scalar Functions. Each one having their own purpose and specific use. Let’s go over a few of them and include some examples.

SQL Aggregate Functions

With a SQL Aggregate Function, you can return a single value, calculated from values in a column. Let’s look at the list of SQL Aggregate Functions.

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum

The general syntax for an aggregate function will follow the guideline : aggregate_function_name ( [ALL | DISTINCT] expression ).

The aggregate function name can be anyone of the functions listed above. The ALL clause is the default behavior (when not specified) and evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function. In most cases you will not need to specify All or Distinct. Let’s look at an example of how to use this guideline.

Here is our table we will be working with which is named Products:

ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

By using the AVG() function, we will be able to get the mathematical average value of a column. By using the following code, we are able to get the Average of the Price column and designate it a new row called PriceAverage

Input the command and you should get an average value of 10. To break it down, the SELECT statement is used to select the data from a database, AVG(Price) to get the average value of the price column, AS PriceAverage to place our value in a new row called PriceAverage and FROM Products to designate which table to pull the data from (if we have multiple tables).

Here is another example. What if we wanted to get the SUM of a table’s column? We would want to use the SUM function on our Products table Price and get the sum of 30.

SQL Scalar Functions

With a SQL Scalar Function, you can return a single value, based on the input value. They are particualrly used to identify the current user session and organizing the tables. Let’s look at the list of some of the SQL Scalar Functions.

  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

Each one of these functions have their own syntax and are worth looking into. The first Scalar Function we will be trying out is the MID() Function. By using the MID() Function we will be able extract characters from a text field inside of our table. The typical syntax guidelines for this function is SELECT MID(column_name,start[,length]) AS some_name FROM table_name;. It is pretty straight forward as you designate the MID() Function and the parameters such as the name of the column you are targeting, the starting point where to count, how many characters to count from the start and give the value a new row as well as the table you are choosing from.

Here is our table we will be working with which is named Products:

ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

If we wanted to select the first three characters of each Product from the ProductName column, our code would look something like this. Input the command and you should get the results with a new row called “ProductAbbr” with App, Ora, and Pea in each row.

Here is another example. What if we wanted to add a row of the time when these products prices were updated? We can use the NOW() function to solve this problem. Here is the code that will create a column called PerDate and display the current date and time.

Wrapping It Up

There are so many more built-in SQL functions that you can utilize in your database management. We have yet to go over Numeric Functions and String Functions which you can look forward to in future lessons. Try practicing setting up your own database and test some of these functions out.