Database Reference
In-Depth Information
+-------------+-------------------+
| 2166 | 216.6000 |
+-------------+-------------------+
• What is the total population of the United States?
mysql> SELECT SUM(pop) FROM states;
+-----------+
| SUM(pop) |
+-----------+
| 308143815 |
+-----------+
The value represents the population reported for the 2010 census. The figure shown
here differs from the US population reported by the US Census Bureau because the
states table contains no count for Washington, D.C.
SUM() and AVG() are numeric functions, so they can't be used with strings or temporal
values. But sometimes you can convert nonnumeric values to useful numeric forms.
Suppose that a table stores TIME values that represent elapsed time:
mysql> SELECT t1 FROM time_val;
+----------+
| t1 |
+----------+
| 15:00:00 |
| 05:01:30 |
| 12:30:20 |
+----------+
To compute the total elapsed time, use TIME_TO_SEC() to convert the values to seconds
before summing them. The resulting sum is also in seconds; pass it to SEC_TO_TIME()
to convert it back to TIME format:
mysql> SELECT SUM(TIME_TO_SEC(t1)) AS 'total seconds',
-> SEC_TO_TIME(SUM(TIME_TO_SEC(t1))) AS 'total time'
-> FROM time_val;
+---------------+------------+
| total seconds | total time |
+---------------+------------+
| 117110 | 32:31:50 |
+---------------+------------+
Using DISTINCT to eliminate duplicates
A summary operation that uses no aggregate functions is determining the unique values
or rows in a dataset. Do this with DISTINCT (or DISTINCTROW , a synonym). DISTINCT
boils down a query result, and often is combined with ORDER BY to place values in more
meaningful order. This query lists in lexical order the drivers named in the driv
er_log table:
Search WWH ::




Custom Search