MySQL Has Functions (Part 3) : Fun With Numbers
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.
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()
CEIL() for short, is used to round up a number to the nearest integer.
So the SQL:
will return a value of 24.
The opposite of
FLOOR() is used to round a number down to the nearest integer. So
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() function will, unsurprisingly, round any number you pass it. Unlike
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
- 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.
As I'm sure you've already worked out,
RAND() will generate a random number for you. The number generated will always be between
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.
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.
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() 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() 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()
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() 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"
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.