Welcome to Part 3 of the series on **MySQL Functions**. In case you haven’t read Part 1 – Date and Time Functions or Part 2 – String Functions, the purpose of this series is to introduce some of the useful functions available in MySQL, so you can make better use of them in your web applications, cutting down the amount of PHP *(or other language)* code you need to write. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.

## Mathematical Functions

I’ve divided this article into two sections, one of which deals with purely mathematical functions, and the other with **aggregate** functions. To learn more on numeric functions, you can checkout the MySQL manual page.

### CEILING() & FLOOR()

`CEILING()`

, or `CEIL()`

for short, is used to round up a number to the nearest integer.

So the SQL:

"SELECT CEIL(23.6)"

will return a value of 24.

The opposite of `CEILING()`

, `FLOOR()`

is used to round a number down to the nearest integer. So

"SELECT FLOOR(23.6)"

will return a value of 23.

So if in your PHP code you ever find yourself rounding up or down a value returned from your database, you can now get the value worked out for you instead and save a little bit of code.

### ROUND()

The `ROUND()`

function will, unsurprisingly, round any number you pass it. Unlike `FLOOR()`

and `CEIL()`

which will only round in one direction, and to the nearest whole number:

- It will round either up or down depending on the number e.g.
`ROUND(1.3)`

returns 1, and`ROUND(1.6)`

returns 2. - It can round to any precision (number of decimal places) you want e.g.
`ROUND(1.23, 1)`

returns 1.2, and`ROUND(23.336, 2)`

returns 23.34.

### RAND()

As I’m sure you’ve already worked out, `RAND()`

will generate a random number for you. The number generated will always be between `0`

and `1`

, *e.g. 0.87564836476393*.

This on its own isn’t very useful, but there are two scenarios in which I’ve used `RAND()`

before. The first scenario is to select a random set of rows from a query like so:

"SELECT * FROM table_name ORDER BY RAND() LIMIT 3"

The above query will select the first 3 rows, but because `RAND()`

is used to order the rows, we don’t know which 3 they will be.

The second scenario is to generate a whole number between two values. So if we want to generate a number between 10 and 100, you would write something like this:

"SELECT 10 + FLOOR(RAND() * 90) AS random_number"

`RAND() * 90`

can result in any value from 0 to 90, so adding 10 to this gets us a number between 10 and 100. We use `FLOOR()`

to make sure the random number is an integer before adding 10.

## Aggregate Functions

Unlike the purely mathematical functions above, aggregate functions are designed to operate on sets of data generated by the `GROUP BY`

statement.

The results of these functions don’t have to be numeric – you can quite often use text fields with them – but most often I find myself using them in a numeric context, and some of them only really work when passed numbers as parameters. To learn more on aggregate function, check out the MySQL manual page.

For these examples I’m going to revisit the table I used in Part 1: Date & Time Functions, the students table. I’ve modified it, however, in order to contain more relevant data for these functions.

### GROUP BY

It probably wouldn’t be fair to go on about aggregate functions without quickly explaining how `GROUP BY`

works, just in case you’ve never come across it before.

Whereas a normal `SELECT`

statement will return a number of separate rows, using `GROUP BY`

in your query will cause those rows to be bundled up together based on the field you specified. Aggregate functions work on those sets of data, rather than the individual rows, and so allow you to calculate values such as minimums, maximums, averages etc.

Let’s see how some of them work.

*NOTE: If you use an aggregate function but don’t use GROUP BY then the function will treat all the rows as one big group.*

### AVG()

The `AVG()`

function calculates the average value of a field in each group. So to calculate the average score of all the students, across every subject, we can use a query like this:

"SELECT AVG(student_mark) AS average_mark FROM students"

As you can see, because no `GROUP BY`

was used, the whole table was treated as one big group and the average of all scores was returned as a single row. If used with `GROUP BY`

it will calculate the average value per set.

"SELECT student_subject, AVG(student_mark) AS average_mark FROM students GROUP BY student_subject"

This time, because we used the `student_subject`

field to group results, we get a row per subject, and an average based on the values in each group.

### COUNT()

`COUNT()`

is one of the more commonly used aggregate functions, which tells you how many items of each type there are in a group.

It’s most often used to simply count how many rows there are in a whole result set like this:

"SELECT COUNT(*) AS num_rows FROM students"

Something a little more advanced would be using it with `GROUP BY`

to find out how many students there are taking each subject:

"SELECT student_subject, count(student_name) AS subject_count FROM students GROUP BY student_subject"

### MIN() and MAX()

`MIN()`

and `MAX()`

will return the lowest or the highest value contained in the field you specify. So to find the highest mark in each subject we could write:

"SELECT student_subject, MAX(student_mark) FROM students GROUP BY student_subject"

And to find the lowest scoring student per gender we would use:

"SELECT student_name, MIN(student_mark) AS lowest_score FROM students GROUP BY student_gender"

### SUM()

The `SUM()`

function simply adds up all the values of the field you specify.

So to calculate the total number of marks in each subject:

"SELECT student_subject, SUM(student_mark) AS total_marks FROM students GROUP BY student_subject"

## Conclusion

I hope you’ve found this quick look at the functions MySQL provides for mathematical and aggregate operations useful. It’s never a very interesting topic as the functions are quite simple to use, but I hope you’ll remember them and be able to use them to make your web development easier. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.

very VERY useful ! a definite bookmark, thank you

Awesome post! Learnt a few things from this, cheers,

James

Very nice indeed !

But you should add a section to your “GROUP BY” entry about the “WITH ROLLUP” modifier.

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

With it on, mysql does the total and grand total for you !

Thomas

Very Useful Article.

Thanks..

Hey thanx for sharing.

Thanks! Great!