Better to know some... than all 


FunctionsFunctions in SQL enable you to perform feats such as determining the sum of a column or converting all the characters of a string to uppercase. You will understand and be able to use all the following: • Aggregate functions • Date and time functions • Arithmetic functions • Character functions • Conversion functions • Miscellaneous functions These functions greatly increase your ability to manipulate the information you retrieved using the basic functions of SQL that were described earlier this week. The first five aggregate functions, COUNT, SUM, AVG, MAX, and MIN, are defined in the ANSI standard. Most implementations of SQL have extensions to these aggregate functions, some of which are covered today. Some implementations may use different names for these functions. Aggregate FunctionsThese functions are also referred to as group functions. They return a value based on the values in a column. (After all, you wouldn't ask for the average of a single field.) The examples in this section use the table TEAMSTATS: COUNTThe function COUNT returns the number of rows that satisfy the condition in the WHERE clause. Say you wanted to know how many ball players were hitting under 350. You would type INPUT: SQL> SELECT COUNT (*) FROM TEAMSTATS WHERE HITS/AB > .35; To make the code more readable, try an alias: INPUT: SQL> SELECT COUNT (*) NUMBELOW350 FROM TEAMSTATS WHERE HITS/AB > .35; Would it make any difference if you tried a column name instead of the asterisk? (Notice the use of parentheses around the column names.) Try this: INPUT: SQL> SELECT COUNT (NAME) NUMBELOW350 FROM TEAMSTATS WHERE HITS/AB > .35; The answer is no. The NAME column that you selected was not involved in the WHERE statement. If you use COUNT without a WHERE clause, it returns the number of records in the table. INPUT: SQL> SELECT COUNT (*) FROM TEAMSTATS; SUMSUM does just that. It returns the sum of all values in a column. SUM works only with numbers.To find out how many singles have been hit, type and to get sum of more columns type, INPUT: SQL> SELECT SUM (SINGLES) TOTALSINGLES FROM TEAMSTATS; INPUT: SQL> SELECT SUM (SINGLES) TOTALSINGLES, SUM (DOUBLES) TOTALDOUBLES, SUM (TRIPLES) TOTALTRIPLES, SUM (HR) TOTALHR FROM TEAMSTATS; AVGThe AVG function computes the average of a column. To find the average number of strike outs, use this: INPUT: SQL> SELECT AVG (SO) AVESTRIKEOUTS FROM TEAMSTATS; The following example illustrates the difference between SUM and AVG: ANALYSIS: The team was batting over 300 in the previous example! What happened? AVG computed the average of the combined column hits divided by at bats, whereas the example with SUM divided the total number of hits by the number of at bats. For example, player A gets 50 hits in 100 at bats for a .500 average. Player B gets 0 hits in 1 at bat for a 0.0 average. The average of 0.0 and 0.5 is .250. If you compute the combined average of 50 hits in 101 at bats, the answer is a respectable .495. The following statement returns the correct batting average: MAXIf you want to find the largest value in a column, use MAX. For example, what is the highest number of hits? INPUT: SQL> SELECT MAX (HITS) FROM TEAMSTATS; Unfortunately, you can't use aggregate functions in where clause, it gives ERROR. The error message is a reminder that this group function (remember that aggregate functions are also called group functions. "Subqueries: The Embedded SELECT Statement," covers the concept of subqueries and explains a way to find who has the MAX hits. What happens if you try a nonnumeric column? INPUT: SQL> SELECT MAX (NAME) FROM TEAMSTATS; Here's something new. MAX returns the highest (closest to Z) string. Finally, a function that works with both characters and numbers. MINMIN does the expected thing and works like MAX except it returns the lowest member of a column. To find out the fewest at bats type and for alphabets the following statement returns the name closest to the beginning of the alphabet: This sort of information can be useful when using statistical functions. INPUT: SQL> SELECT MIN (AB) FROM TEAMSTATS; INPUT: SQL> SELECT MIN (NAME) FROM TEAMSTATS; NOTE: As we mentioned in the introduction, the first five aggregate functions are described in the ANSI standard. The remaining aggregate functions have become de facto standards, present in all important implementations of SQL. We use the Oracle7 names for these functions. Other implementations may use different names.
VARIANCEVARIANCE produces the square of the standard deviation, a number vital to many statistical calculations. It works like this: INPUT: SQL> SELECT VARIANCE (HITS) FROM TEAMSTATS; STDDEVThe final group function, STDDEV, finds the standard deviation of a column of numbers, as demonstrated by this example: INPUT: SQL> SELECT STDDEV (HITS) FROM TEAMSTATS; These aggregate functions can also be used in various combinations: INPUT: SQL> SELECT COUNT (AB), AVG (AB), MIN (AB), MAX (AB), STDDEV (AB), VARIANCE (AB), SUM (AB) FROM TEAMSTATS; 