This function adds all the values together and returns the sum.STD()
The STD() function returns the standard deviation of a given column, as shown in the following:
SELECT STD(Price)
FROM Orders
Note Standard deviation is a measure of dispersal. It is used to gauge how far from the
average a value is. It can be expressed mathematically as follows:
Sqrt E[(X-E(X))2]
where E is a known point.Aggregate Functions and the WHERE Clause
Remember that a WHERE clause could also be used in any of the previous examples. This could limit what
values are calculated in the function. Using the WHERE clause with these functions gives you a powerful set
of tools to perform most any financial or scientific calculation.
Aggregate Functions and the GROUP BY Clause
MySQL also supports the GROUP BY clause. The GROUP BY clause can only be used in conjunction with an
aggregate function. The GROUP BY clause allows you to group a set of results together. For example,
suppose you wanted to know the number of orders a person placed in a given day. You could use the
following statement:
SELECT C.First_Name, C.Last_Name, O.Order_Date,
Count(C.Customer_ID)AS Orders
FROM Customer as C
JOIN Orders as O ON C.Customer_ID = O.Customer_ID
WHERE O.Order_Date = '2000-01-13'
GROUP BY C.First_Name
The resultset would look similar to Table 9.3.
Table 9.3 Aggregate Functions with GROUP BY
First_Name Last_Name Order_Date OrdersTaylor Smith 2000-01-13^3Sydney Sue 2000-01-13^1Stan Behm 2000-01-13 6
Notice that the results are grouped together based on the column name in the GROUP BY clause. The
GROUP BY clause is an invaluable tool when generating reports.Sorting
Most RDBMs provide a way for the user to sort results. MySQL is no different. To sort a resultset based on a
given column, you can use the ORDER BY clause. The ORDER BY clause causes a resultset to be sorted by
the column you name and the directions you specify. For example, if you wanted to sort the previous
example by last name then by first name in descending order, you would issue the following statement:
SELECT C.First_Name, C. Last_Name, O.Order_Date
Count(C.Customer_ID) AS Orders
FROM Customer as C
JOIN Orders as O ON C.Customer_ID = O.Customer_ID
WHERE O.Order_Date = '2000-01-13'
GROUP BY C.First_Name
ORDER BY Last_Name, First_Name DESC
If you wanted to sort your data in ascending order, replace the DESC with ASC. That's all there is to it. To
sort Text and BLOB type columns, you must ORDER BY a fixed length part of the data. To do this, you
would use the SUBSTRING function. This is covered in tomorrow's lesson.
In the example, you have two column names in the ORDER BY clause. The ORDER BY will sort the
column primarily by the first column and then by the second column. This follows the natural order of
things—you generally would sort names by the last name and, if two people had the same last name,