Previous Page Next Page

B.4. SQL Functions

The following functions are used to alter or format the output of a SQL query. Functions are provided for strings, numbers, dates, server and information, and so on. They return a result set. Functions are vendor specific, meaning functions supported by MySQL might not be supported by Microsoft SQL Server. See the MySQL documenation for a list of all functions supported.

When using SELECT with a function, the function, as it was called, is displayed as the name of the column in the result set as shown in Example B.36.

Example B.36. 

1   mysql> SELECT avg(UnitPrice)
    FROM order_details;
    +----------------+
    | avg(UnitPrice) |
    +----------------+
    |    26.21851972 |
    +----------------+
    1 row in set (0.01 sec)

2   mysql> SELECT avg(UnitPrice) as 'Average Price'
    FROM order_details;
    +---------------+
    | Average Price |
    +---------------+
    |   26.21851972 |
    +---------------+
    1 row in set (0.00 sec)

Explanation

  1. The function is displayed as the name of the column.

  2. You can use the AS keyword to create an alias or another name for the column where the function displays the result set.

B.4.1. Numeric Functions

Suppose you want to get the sum of all the orders or the average cost of a set of items or to count all the rows in a table based on a certain condition. The aggregate functions will return a single value based on a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The functions and their syntax are listed in Table B.4.

Table B.4. Aggregate Functions
FunctionWhat It Does
AVG()Computes and returns the average value of a column.
COUNT(expression)Counts the rows defined by the expression.
COUNT()Counts all rows in a table.
MIN()Returns the minimum value in a column.
MAX()Returns the maximum value in a column by the expression.
SUM()Returns the sum of all the values in a column.


Example B.37. 

1   mysql> select count(*) from products;
    +----------+
    | count(*) |
    +----------+
    |       81 |
    +----------+
    1 row in set (0.00 sec)

    mysql> SELECT count(*) as 'Number of Rows' FROM products;
    +----------------+
    | Number of Rows |
    +----------------+
    |             81 |
    +----------------+
    1 row in set (0.00 sec)

Explanation

  1. The COUNT() function counts all rows in a table.

Example B.38. 

1   mysql> SELECT avg(UnitPrice)
    FROM order_details;
    +----------------+
    | avg(UnitPrice) |
    +----------------+
    |    26.21851972 |
    +----------------+
    1 row in set (0.01 sec)

2   mysql> SELECT FORMAT(avg(UnitPrice),2) as 'Average Price'
    FROM  order_details;
    +---------------+
    | Average Price |
    +---------------+
    | 26.22         |
    +---------------+
    1 row in set (0.00 sec)

Explanation

  1. The AVG() function computes and returns the average value of a column, called UnitPrice.

  2. The FORMAT function returns the result of the AVG() function with a precision of two decimal places.

Using GROUP BY

The GROUP BY clause can be used with a SELECT to collect all the data across multiple records and group the results by one or more columns. This is useful with the aggregate functions, such as SUM, COUNT, MIN, or MAX. See the following two examples.

Example B.39. 

mysql> select CategoryID, SUM(UnitsInStock) as 'Total Units in Stock'
    -> FROM products
    -> GROUP BY CategoryID;
+------------+----------------------+
| CategoryID | Total Units in Stock |
+------------+----------------------+
|       NULL |                    0 |
|          1 |                  559 |
|          2 |                  507 |
|          3 |                  386 |
|          4 |                  393 |
|          5 |                  308 |
|          6 |                  165 |
|          7 |                  100 |
|          8 |                  701 |
+------------+----------------------+
9 rows in set (0.00 sec)

Example B.40. 

mysql> select C.CategoryName,
    -> SUM(P.unitsInsStock) AS Units
    -> FROM products as P
    -> join categories AS C ON C.CategoryID=
    -> P.CategoryID Group By C.CategoryName;
+----------------+-------+
| CategoryName   | Units |
+----------------+-------+
| Beverages      |   559 |
| Condiments     |   507 |
| Confections    |   386 |
| Dairy Products |   393 |
| Grains/Cereals |   308 |
| Meat/Poultry   |   165 |
| Produce        |   100 |
| Seafood        |   701 |
+----------------+-------+
8 rows in set (0.00 sec)

B.4.2. String Functions

SQL provides a number of basic string functions, as listed in Table B.5.

Table B.5. MySQL String Functions
FunctionWhat It Does
CONCAT(string1,string2,...)[a]Combines column values, or variables, together into one string.
LOWER(string)Converts a string to all lowercase characters.
SUBSTRING(string, position)Extracts a portion of a string (see Example B.41).
TRANSLATEConverts a string from one character set to another.
TRIM(' string ');Removes leading characters, trailing characters, or both from a character string.
UPPER(string)Converts a string to all uppercase characters (see Example B.41).


[a] SQL99 defines a concatenation operator (||) to use with the CONCATENATE() function. MySQL uses the concat() function shown in Table B.5.

Example B.41. 

mysql> select upper(CompanyName) as 'Company' from shippers;
+------------------+
| Company          |
+------------------+
| SPEEDY EXPRESS   |
| UNITED PACKAGE   |
| FEDERAL SHIPPING |
+------------------+
3 rows in set (0.00 sec)

mysql> select lower(CompanyName) as 'Company' FROM shippers;
+------------------+
| Company          |
+------------------+
| speedy express   |
| united package   |
| federal shipping |
+------------------+
3 rows in set (0.00 sec)

B.4.3. Date and Time Functions

To get the date and time, MySQL provides the functions shown in Table B.6.

Table B.6. MySQL Date and Time Functions
FunctionExample
NOW()
select NOW()
--> 2006-03-23 20:52:58

(See Example B.42.)
CURDATE()
select CURDATE();
--> '2006-12-15'

(See Example B.42.)
CURTIME()
select CURTIME();
--> '23:50:26'

(See Example B.42.)
DAYOFYEAR(date)
select DAYOFYEAR('2006-12-15');
--> 349

DAYOFMONTH(date)
select DAYOFMONTH('2006-12-15');
--> 15

DAYOFWEEK(date)
select DAYOFWEEK('2006-12-15');
--> 6

WEEKDAY(date)
select WEEKDAY('2006-12-15');
--> 4

MONTHNAME(date)
select MONTHNAME('2006-12-15');
--> December

DAYNAME(date)
select DAYNAME('2006-12-15');
--> Friday

YEAR(date)
select YEAR('2006-12-15');
--> 2006

QUARTER(date)
select QUARTER('2006-12-15');
--> 4


Example B.42. 

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-03-21 00:32:37 |
+---------------------+
1 row in set (0.00 sec)

mysql> select CURDATE();
+----------------+
|    CURDATE()   |
+----------------+
| 2006-03-21     |
+----------------+
1 row in set (0.03 sec)

mysql> select CURTIME();
+----------------+
|    CURTIME()   |
+----------------+
| 00:12:46       |
+----------------+
1 row in set (0.01 sec)

Formatting the Date and Time

When retrieving dates and times from a table, you might find you want to format the output. For example, when selecting the dates of the orders from the orders table in the northwind database, the result set is not user friendly. Date values in SQL are always saved in MM/DD/YY(YY) format. The DATE_FORMAT() and TIME_FORMAT() functions (see Example B.43) are provided with a list of parameters (see Table B.7) used to specify how the output should be displayed.

Example B.43. 

mysql> select DATE_FORMAT('2006-03-23', '%W %M %d, %Y') as Today;
+-------------------------+
| Today                   |
+-------------------------+
| Thursday March 23, 2006 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select DATE_FORMAT(OrderDate,'%M %e, %Y - %l:%i %p')
             FROM orders LIMIT 5;

+-----------------------------------------------+
| DATE_FORMAT(OrderDate,'%M %e, %Y - %l:%i %p') |
+-----------------------------------------------+
| July 4, 1996 - 12:00 AM                       |
| July 5, 1996 - 12:00 AM                       |
| July 8, 1996 - 12:00 AM                       |
| July 8, 1996 - 12:00 AM                       |
| July 9, 1996 - 12:00 AM                       |
+-----------------------------------------------+
5 rows in set (0.00 sec)

Table B.7. DATE_FORMAT() and TIME_FORMAT()
ParameterWhat It Means
%aWeekday abbreviation (Sun, Mon, Tues, etc.)
%bMonth name abbreviation (Jan, Feb, Mar, etc.)
%cMonth (1–12)
%dTwo-digit day of the month (01–31)
%DDay with a suffix (30th, 31st)
%eDay of the month (1–31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%iMinutes, numeric (00..59)
%IHour (01–12)
%jDay of year (001–366)
%kHour (0..23)
%lHour (1–12)
%mMonth with a leading 0 (01, 06, etc.)
%MMonth name (March, April, May, etc.)
%pAM/PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53) starting with Sunday
%uWeek (00..53) starting with Monday
%vWeek (01..53) starting with Monday
%VWeek (01..53) starting with Sunday
%WWeekday (Sunday, Monday, etc.)
%wDay of the week (0 = Sunday..6 = Saturday)
%YYear (1999, 2007)
%yTwo-digit year (99, 07)
%%A literal % character


The MySQL EXTRACT Command

The EXTRACT command is an example of a MySQL extension, not described in the SQL standard. It allows you to extract different parts of a date or time, as shown in Table B.8.

Table B.8. Date and Time Parts
TypeFormat
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
MONTHMONTHS
YEARYEARS (see Example B.44)
MINUTE SECOND"MINUTES:SECONDS"
HOUR_MINUTE"HOURS:MINUTES"
DAY_HOUR"DAYS HOURS"
YEAR_MONTH"YEARS-MONTHS"
HOUR_SECOND"HOURS:MINUTES:SECONDS"
DAY_MINUTE"DAYS HOURS:MINUTES"
DAY_SECOND"DAYS HOURS:MINUTES:SECONDS"


Example B.44. 

mysql> select EXTRACT(YEAR FROM NOW());
+--------------------------+
| EXTRACT(YEAR FROM NOW()) |
+--------------------------+
|                     2006 |
+--------------------------+
1 row in set (0.03 sec)

Previous Page Next Page