MySQL Has Functions (Part 3) : Fun With Numbers

by in Development on 15th Jun 2010 · Comments

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.

MySQL Has Functions (Part 3)

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:

  1. It will round either up or down depending on the number e.g. ROUND(1.3) returns 1, and ROUND(1.6) returns 2.
  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.

MySQL Function Aggregate 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:

MySQL AVG Function

"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"
 

MySQL Function Group By

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"

MySQL Function Count Row

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"

MySQL Function Count Group By

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"
 

MySQL Function Max

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"

MySQL Function Min

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"

MySQL Function Sum

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.

Jonathan Phillips is a web developer from the UK, specializing in PHP & MySQL plus HTML & CSS (of course). He also dabble in Internet marketing (SEO & PPC) and design. When not writing articles for Onextrapixel and his own blog, divisionbyzero.co.uk, he works at his company, Phillips Internet Limited.