You already know about functions in PHP, but in this chapter, you'll see that SQL also has its own set of functions! That's right, SQL lets you run calculations directly on your data using handy built-in functions.

There aren't as many as in PHP, but these are tailor-made for databases and prove incredibly powerful in real-world use. Going back to our example table, video_games, SQL functions can easily give us the average price of all the games, count how many games each person owns, find the most expensive or the cheapest game, and more. These functions also come in super handy when dealing with dates in SQL, which we'll dive into in the next chapters.

SQL functions generally fall into two categories:

• Scalar functions: These work on each individual entry. For example, you can convert every value in a field to uppercase.
• Aggregate functions: These run calculations on the whole table and return a single result. For instance, calculating the average price returns one value: the average.

Scalar Functions

Let's start by looking at how a scalar SQL function works — specifically, the UPPER function. Once you know how to use this one, you'll be able to handle all the other scalar functions the same way. I'll show you a few of the most useful ones, but just so you know, there are plenty more out there — we simply don't have time to cover them all. ;)

Using a Scalar SQL Function

We'll use our good old video_games table for examples. Here's a quick refresher on what it looks like:

To show you how a scalar SQL function works, let's use UPPER() which converts a field's text to all uppercase letters. Say we want to get all the game names in uppercase. Here's the SQL query:

SELECT UPPER(name) FROM video_games

The UPPER function is applied to the name field. The result? We get all the game names in full caps.

Nope! The table stays exactly the same. UPPER only changes the value sent back to PHP — it doesn't modify the table's data.

What it does is create a "virtual field" that only exists during the query. It's best practice to give this virtual field a name using the AS keyword, like this:

SELECT UPPER(name) AS name_upper FROM video_games

Now we're retrieving the uppercase names in a virtual field called name_upper.

This virtual field is called an alias.

Here's what MySQL will return based on that query:

name_upper
SLY 3: HONOR AMONG THIEVES
SONIC
ZELDA: OCARINA OF TIME
SILENT HILL 3
GEARS OF WAR II

You can then use this in PHP to display all the game names in uppercase:

<?php
$response = $db->query('SELECT UPPER(name) AS name_upper FROM video_games');
while ($data = $response->fetch()) {
    echo $data['name_upper'] . '<br />';
}
$response->closeCursor();
?>

As you can see, PHP only gets one field named name_upper (even though it doesn't really exist in the table). When we display it, we get the game names in uppercase.

Of course, you can also retrieve other fields like normal without applying any function to them:

SELECT UPPER(name) AS name_upper, owner, console, price FROM video_games

This would return something like:

Now you know how to use a scalar SQL function. :)

Let's check out a few more useful ones that work exactly the same way.

A Few Handy Scalar Functions

Here's a quick rundown of some scalar functions that are good to know. There are a bunch more out there — we'll mention that at the end — but listing them all here would take forever and wouldn't be all that useful.

UPPER: convert to uppercase

This function turns the text in a field into uppercase letters. We already used it as our first example:

SELECT UPPER(name) AS name_upper FROM video_games

So the game "Sonic" would be returned as "SONIC" in the name_upper field.

LOWER: convert to lowercase

This one does the opposite: it turns all the text into lowercase.

SELECT LOWER(name) AS name_lower FROM video_games

Now, "Sonic" becomes "sonic" in a field called name_lower.

LENGTH: count the number of characters

Use the LENGTH() function to get how many characters are in a field:

SELECT LENGTH(name) AS name_length FROM video_games

So for "Sonic", we'd get the value 5 in the field name_length.

ROUND: round a decimal number

ROUND() is used on fields that have decimal values. We don't have decimals in our video_games table, but if prices had cents, this function would come in handy.

It takes two parameters: the field to round and the number of digits after the decimal point you want.

Example:

SELECT ROUND(price, 2) AS rounded_price FROM video_games

So if a game costs 29.86999 USD, it'll be returned as 29.87 in the rounded_price field.

And many more!

There are loads of other scalar SQL functions, but I won't list them all here. The MySQL documentation has a much more complete list of math functions (like ROUND) and string functions (like UPPER). If you want to explore more, that's the place to go!

Aggregate Functions

Just like before, we'll first take a look at how to use an aggregate function in an SQL query and how to fetch the result in PHP. Then I'll introduce you to a few key functions worth knowing. Of course, there are many others, which you'll be able to explore in the documentation. What matters most is understanding how this kind of function works — once you get that, you'll be able to use any other function of the same type. :)

Using an SQL Aggregate Function

These functions are quite different from the scalar ones. Instead of working on each value one by one, aggregate functions operate on multiple entries at once to return a single result.

For example, the ROUND function lets you round each price. You get as many results as there are entries in the table. But an aggregate function like AVG returns just one result: the average value of all prices.

Let's take a close look at the AVG function. It calculates the average of a column filled with numbers. Let's apply it to the price column:

SELECT AVG(price) AS average_price FROM video_games

Here again, we give the result an alias. What's special here is that this query will return only one row — the average price of all the games:

average_price  
29.34

To display this in PHP, we can use the same method we're already familiar with (it works just fine):

<?php
$response = $db->query('SELECT AVG(price) AS average_price FROM video_games');
while ($data = $response->fetch())
{
    echo $data['average_price'];
}
$response->closeCursor();
?>

But honestly — why bother with a loop if we already know we're only getting one row back, since it's an aggregate function?

We can just call fetch() once, outside of any loop. So this version is a bit cleaner and better suited to our needs:

<?php
$response = $db->query('SELECT AVG(price) AS average_price FROM video_games');
$data = $response->fetch();
echo $data['average_price'];
$response->closeCursor();
?>

This code is simpler and more logical. We fetch the first (and only) result with fetch(), print it out, and then close the cursor. No need to loop through anything — there is no second result.

Don't Forget You Can Filter!

Naturally, you can take full advantage of SQL's power to do things like get the average price of games owned by Patrick. Here's how:

SELECT AVG(price) AS average_price FROM video_games WHERE owner='Patrick'

The average will be calculated only for the games that belong to Patrick. You could even combine filters — like getting the average price of Patrick's games that are single-player. Go ahead, try it!

Don't Mix Aggregate Functions with Other Columns

Here's something to watch out for — it's not always obvious: when using an aggregate function, you shouldn't try to retrieve other columns from the table at the same time. Unlike scalar functions, aggregate functions return a single value. So doing something like this:

SELECT AVG(price) AS average_price, name FROM video_games

...makes no sense. You'd be getting, on the one hand, the average of all the game prices, and on the other, a list of all the game names? That can't be displayed in a single result row.

A Few Useful Aggregate Functions

Here are a few aggregate SQL functions you can use.

AVG – Calculate an Average

This is the function we just looked at to introduce aggregate functions. It returns the average value of a numeric column:

SELECT AVG(price) AS average_price FROM video_games

SUM – Add Up All Values

The SUM function adds together all the values in a given column. For example, to find the total value of Patrick's games:

SELECT SUM(price) AS total_price FROM video_games WHERE owner='Patrick'

MAX – Return the Maximum Value

This function goes through a column and returns the highest value it finds. To get the price of the most expensive game:

SELECT MAX(price) AS max_price FROM video_games

MIN – Return the Minimum Value

Same idea here, but it gives you the smallest value instead:

SELECT MIN(price) AS min_price FROM video_games

COUNT – Count the Number of Entries

The COUNT function tells you how many rows there are. It's super useful — but also a bit more complex. You can use it in a few different ways.

The most common usage is with an asterisk:

SELECT COUNT(*) AS game_count FROM video_games

This gives us the total number of games in the table.

Of course, we can filter using a WHERE clause — for instance, to get the number of games owned by Phil:

SELECT COUNT(*) AS game_count FROM video_games WHERE owner='Phil'

You can also count only the entries where a specific field is not empty — that is, where it's not NULL. There aren't any such entries in our video_games table right now, but let's imagine that for some games, we don't know the maximum number of players. We'd leave some fields blank, which would show up as NULL in the max_players column, like this:

In this case, we can count only the games that do have a known value for max_players. You just pass the field name as a parameter:

SELECT COUNT(max_players) AS game_count FROM video_games

In our example, only Zelda and Silent Hill will be counted, since those are the only ones with a defined maximum number of players. So the result will be 2.

DISTINCT: Unique Entries

Finally, you can count how many distinct values exist in a particular field. In the owner column, for example, you'll see Phil and Patrick appear multiple times. But how many different people are listed in the table? To find out, use the DISTINCT keyword before the field name:

SELECT COUNT(DISTINCT owner) AS owner_count FROM video_games

That's how you can easily find out how many unique people are referenced in the table. Try doing the same to find out how many different consoles are listed!

Grouping Data with SQL

I mentioned earlier that you can't retrieve other columns when using an aggregate function. Take this query, for example:

SELECT AVG(price) AS average_price, console FROM video_games

It doesn't make sense to retrieve the average price of all games and the console field at the same time. There's only one average price across all games, but several consoles. MySQL won't be able to return a coherent result table for that.

If you try running this query, you'll get weird results:

Okay, the average price is right — but why Xbox? Why not another console? It doesn't mean anything.

GROUP BY: Grouping Data

However, something that would make sense is asking for the average game price per console. To do that, we use a new keyword: GROUP BY. You use it in combination with an aggregate function (like AVG) to pull interesting info from groups of data.

Here's how you use GROUP BY:

SELECT AVG(price) AS average_price, console FROM video_games GROUP BY console

You have to use GROUP BY along with an aggregate function; otherwise, it's pointless. In this case, we're getting both the average price and the console — but we're grouping by console. As a result, we'll get a list of each console and the average price of the games available for each one!

Now the results make sense! You get a list of consoles, and the average price of the games for each one.

Exercise: Try doing the same thing, but to get the total value of games owned by each person.

HAVING: Filtering Grouped Data

HAVING is kind of like WHERE, but it filters data after it's been grouped. So it's a way to filter the results at the end of your operations.

Take a look at this query:

SELECT AVG(price) AS average_price, console FROM video_games GROUP BY console HAVING average_price <= 10

This query gives you only the list of consoles where the average game price doesn't exceed 10 euros.

HAVING should only be used on the result of an aggregate function. That's why here we use it on average_price, and not on console.

Yes, but not at the same stage. WHERE acts first, before the data is grouped. HAVING acts after, once grouping is done. In fact, you can combine them! Check this out:

SELECT AVG(price) AS average_price, console 
FROM video_games 
WHERE owner='Patrick' 
GROUP BY console 
HAVING average_price <= 10

Now that's a mighty query. 😄

Here, we're asking for the average price per console of all of Patrick's games (WHERE), but only if the average price of those games doesn't go over 10 dollars (HAVING).

SQL functions open up a whole world of possibilities, as you've probably noticed.

Sure, you might think some things could be handled just as easily in PHP — for example, converting game names to uppercase could just be done at display time using a PHP function. And yes, that's true! But SQL functions are often more flexible and better suited, because you get back already-formatted results straight from the database. Plus, when you start combining aggregate functions with data grouping, you can extract powerful insights even from the simplest of tables.

Using GROUP BY and HAVING isn't always well understood — I've seen people try to use them with scalar functions, which doesn't make sense. So take the time to really understand what they do, and don't use them just anywhere. Always ask yourself, "What exactly do I want to get?" and build your query piece by piece. Trying to write it all in one go can be tricky!

Enjoyed this PHP & MySQL course?

If you liked this lesson, you can find the book "How to Build a Website in HTML and CSS" from the same authors, available on SiteRaw, in bookstores and in online libraries in either digital or paperback format. You will find a complete PHP & MySQL workshop with many exclusive bonus chapters.

More information