There is much debate as to whether or not data calculations should be done in the web app or in the database. There are good arguments on both sides. Depending on what is being calculated, executing calculations in some web programming languages tends to be faster than doing the same thing in the database. However, keeping the calculation in SQL means that the functionality is portable, and often means that less data needs to be retrieved from the database. Why not give yourself a choice and add MySQL functions and calculations to your coding arsenal?
Here are five ways to get more power out of your MySQL SELECT statements.
Casting Data Types
Unless you are a Perl coder, inadvertently using the wrong data type in some bit of code can be a frustrating source of errors, and often this data comes from the database. You should have error checking in place for when data is inserted into the database, but if the database has been around for awhile (i.e. somebody else built it), the data inside might not be completely clean. So you should also make sure that the data being pulled from the database is of the correct data type.
The CAST() function changes the data type of a given value. It can be used to change a decimal to an integer for instance, a character value to a number, or a datetime to a date:
SELECT CAST(price AS DECIMAL) FROM products;
SELECT CAST(isbn AS CHAR(13)) FROM books;
SELECT CAST(NOW() as DATE);
In the last example, the convenient NOW() function is used, which normally contains the current date and time. By using the CAST() function, just the current date portion is returned.
Conversely, if you are inserting data using a programming language that has loose data types (say Perl or PHP), you can make sure the correct data gets in the table by using a cast:
$sql = “INSERT INTO address (zip) VALUES (CAST(‘$zip’ AS UNSIGNED INTEGER));”
The above is a line of PHP code that sets the text of a SQL statement to the variable $sql. The $zip variable is cast as an integer.
Let’s say you have a table that stores the first and last names of a person. It’s common to separate names into different columns so that you can easily search by last name or retrieve just the first name to create a more personalized page for a user. If you then need to retrieve both names and append them together from the result set, you might end up with several lines of code to string them together. However, the two columns can be concatenated within the SQL statement:
SELECT CONCAT(first_name, ‘ ‘, last_name) FROM people;
Here, the column named ‘first_name’ is appended with a space, and that is further appended with the ‘last_name’ column. Any number of columns and strings may be concatenated together. If any of the column values happens to be a NULL, then entire result is a NULL. The CONCAT() function is specific to MySQL, so beware of it if you need to port your code to a different database.
Alternatively, the CONCAT_WS() function will concatenate any number of columns and strings, automatically inserting a defined glue string:
SELECT CONCAT_WS(‘, ‘, first_name, last_name, person_id) FROM people;
In the CONCAT_WS() function, the first argument is the glue string, in this case a comma followed by a space. The glue string is inserted between each of the following given values. This is a useful function if you need to whip up comma delimited data for export. Unlike the CONCAT() function, the CONCAT_WS() function will ignore any NULL values passed to it.
MySQL can easily handle the basic math of addition, subtraction, multiplication, and division. For instance, let’s say you want to return a sale price for an item in a catalog table that just has a list price:
SELECT list_price * 0.70 FROM products;
For clarity in the code, you may want to specify it as a sale price using an alias:
SELECT list_price * 0.70 AS sale_price FROM products;
This won’t have much effect on the result set as one column is still being returned, but it makes the intent of the SQL much clearer at a glance.
Incidentally, the MySQL command prompt can also be used as a calculator in a pinch if you are so inclined:
Using the SELECT command by itself at the MySQL prompt without referencing any tables or columns is a simple way to test out functions and figure out how they will handle particular situations (for example, you can use it to show how a function may handle a NULL value).
MySQL has a slew of sophisticated and handy higher math functions at its disposal. Left out of the basic math operators is modulus (the remainder from a division calculation), and it is represented by the MOD() function:
SELECT CAST(runtime/60 AS UNSIGNED INTEGER) AS hours, runtime MOD 60 AS minutes FROM movies;
The above query returns two fields, hours and minutes. A CAST() function is used to convert the hours from a decimal value to an integer so that the remainder does not appear after a decimal point in the hours value.
The ABS() function returns the absolute value of a given argument:
SELECT ABS(length â€“ width) AS difference FROM measurements;
Here the ABS() function is used to make sure that the returned value is positive in an instance where a negative value is irrelevant. On the other hand, the SIGN() function returns the sign of a number, when the number itself is irrelevant, but the sign (positive or negative) is not.
The RAND() function can be used to produce a random number expressed as a decimal fraction. It can be used with or without a seed argument (computers aren’t genuinely random, so a seed value is used to further randomize the output). It’s like that RAND() isn’t as useful as the random number generator in your application language,
The SQRT() function returns the square root of a single given value. The opposite of SQRT() is POWER(), which takes two arguments, the number, and the power to raise it to.
MySQL also has several common functions used with geometry. The SIN(), COS(), and TAN() functions all take one argument, and return the corresponding sine, cosine, and tangent. The ASIN(), ACOS(), and ATAN() functions also take one argument and return the arc sine, arc cosine, and arc tangent of the given number. Additionally, the COT() function which returns the cotangent of the given argument. There is also the PI() function which takes no arguments, and simply returns the value of pi:
SELECT POWER((0.5 * diameter), 2) * PI() AS circle_area FROM measurements;
Here the area of a circle is calculated using the PI() and POWER() functions and a given diameter value.
One of the most useful of the aggregate functions is COUNT(). The COUNT() function returns the number of rows in a table:
SELECT COUNT(*) FROM people;
The above counts the number of people listed in the table. If the argument is a specific column, it will count all of the rows that do not have NULL in that attribute. By comparing the number of rows to the number of filled values, you can see if your table structure is efficient:
SELECT COUNT(*) AS rows, COUNT(middle_name) FROM people;
The SUM() function is another powerful aggregate function. It is used with numbers, and adds together all the values in a column:
SELECT SUM(gross_income) FROM monthly_revenue WHERE year=2009;
The query above would return the total of all the income recorded in the table monthly_revenue for the year 2009.
SELECT (SUM(gross_income) â€“ SUM(expenses)) AS net_income FROM monthly_revenue WHERE year=2009;
In the above query, the net income for the year is quickly calculated. Again, this simplifies the amount of information returned in the result set for the query when it is handed off to the web application layer.
The AVG() function calculates the average value of a given numeric column:
SELECT AVG(gross_income) AS average_monthly_income FROM monthly_revenue WHERE year=2009;
Here the average monthly income for a give year is calculated. Calculating an average in the database is simpler than averaging the values from an array of potentially unknown length in most web programming languages.
The MAX() and MIN() functions can be used to return the maximum and minimum values for a given column:
SELECT MAX(temp) AS high, MIN(temp) AS low FROM places WHERE city=’McMurdo Station’;
In the above example, the all-time high and low temperatures for McMurdo Station in Antarctica are returned. This is just two values in one row in the returned result set.
Finally, MySQL provides several functions useful with statistics. The STDDEV_POP(), STDDEV_SAMP(), VAR_POP(), and VAR_SAMP() functions return the population standard deviation, the sample standard deviation, the population deviation variance, and the sample deviation variance of a given column. Writing the equivalent code in a web application language could be time consuming, but even if you want to write your own for the sake of having a quick query, it’s handy to be able to check your output against what the database produces.