SQL Functions

SQL also provides powerful ways to summarize data since it allows users to count, compute averages, sums and so on. For example, when the Gym Owner wants to know how much money came in during the months of August and September 2006, the following query would give him this information (Don't forget to substitute the ' with # if you want to run this query on Access). The AS keyword provides an alternate name or ALIAS for the value we are calculating. Columns can also be renamed temporarily using the AS keyword

SELECT SUM(PaymentAmount) AS TotalAmount from Memberpayment WHERE paymentDate >=08-01-2006 AND paymentDate <=09-30-2006

Screen Shot 7d: Result of the sum(PaymentAmount) query

Following is a table that lists common SQL aggregate functions. Try out these examples in MS Access to see the results

Function	Example	Explanation (Result)
AVG(column)	SELECT AVG(PaymentAmount) from MemberPayment where PaymentExpire >=10-2-2006	The average fees paid by a current member
COUNT(column)	SELECT Count(MemberID) from MemberPayment where PaymentExpire >=10-2-2006	The total number of current members
MAX(column)	SELECT MAX(PaymentAmount) from MemberPayment where PaymentExpire >=10-2-2006	The highest membership payment for all current memberships
MIN(column)	SELECT MIN(PaymentAmount) from MemberPayment where PaymentExpire >=10-2-2006	The lowest membership payment for all current memberships
SUM(column)	SELECT SUM(PaymentAmount) from MemberPayment where PaymentExpire >=10-2-2006	The total amount paid by all current members

Other than aggregate functions, SQL also offers stand alone functions like the function CURRENT_DATE that we discussed earlier. Here are some key functions:

Function	Usage
CURRENT_DATE	Identifies the current date.
CURRENT_TIME	Identifies the current time.
CURRENT_TIMESTAMP	Identifies the current date and time.
CONCATENATE (expression || expression)	Appends two or more literal expressions, column values, or variables together into one string.
LOWER	Converts a string to all lowercase characters.
TRIM	Removes leading spaces, trailing spaces, or both from a character string.
UPPER	Converts a string to all uppercase characters.

For example, the following command will yield the member names and addresses separated by commas:

SELECT CONCATENATE(MemberName || ', ' || MemberAddress) from GymMembers

SELECT UPPER(MemberName)

The above listed command will print out a list of Member Names in all capital letters. Along with it, different DBMS offer a wide range of extra functions that are not part of the SQL standards.

SQL also offer a GROUP BY clause that allows totals and aggregates by category. The following query would give sorted day by day collections totals:

SELECT PaymentDate, sum(PaymentAmount) AS TotalPayments from Memberpayment GROUP BY PaymentDate ORDER by paymentdate

Screen Shot 7e: Result of the GROUP BY query

When selecting values for the column that is used to organize values, a HAVING clause should be used in GROUP by queries instead of the WHERE clause. HAVING refers to each group. The following query sorts collections totals by day for payments made before July 2006

SELECT PaymentDate, sum(PaymentAmount) AS TotalPayments from Memberpayment GROUP BY PaymentDate HAVING PaymentDate < '07-01-2006' ORDER by paymentdate

Screen Shot 7f: Result of the GROUP BY - HAVING query

GROUP BY and aggregate functions are used extensively in statistics and analysis related tasks. For this reason, many DBMS offer additional aggregate functions that calculate standard deviations, variance and so on. At this point, we have a good idea of what SQL has to offer in order to help us analyze and modify data. We can now move on to using SQL in procedures and programs.