This topic includes the following sections. For links to more information on xfODBC’s support for SQL, see Appendix B: SQL Support.
An aggregate function derives a single value from a set of values from a column. Aggregate functions must be used with SELECT or HAVING clauses. For example:
SELECT in_color, COUNT(in_color) FROM plants GROUP BY in_color SELECT or_customer, AVG(or_qty) FROM orders GROUP BY or_customer HAVING AVG(or_qty) > 65
The following, however, cause errors because an aggregate function cannot be in a GROUP BY clause (unless it’s contained in a HAVING clause), an ORDER BY clause, or a WHERE clause:
SELECT or_customer, AVG(or_qty) FROM orders GROUP BY AVG(or_qty) SELECT or_customer, AVG(or_qty) FROM orders GROUP BY or_customer ORDER BY AVG(or_qty) SELECT or_customer, AVG(or_qty) FROM orders WHERE AVG(or_qty) > 65 GROUP BY or_customer
If a function is listed in the SELECT statement’s column list, you can reference it by its ordinal position in the column list or in a WHERE or ORDER BY clause. (The SQL syntax parser does not currently allow aggregate functions, other than select list functions, to be referenced in ORDER BY clauses.)
Aggregate functions cannot be used with individual columns in a SELECT statement’s column list unless accompanied by a GROUP BY command that groups by individual column. For example:
SELECT or_customer, SUM(or_qty) FROM orders GROUP BY or_customer SELECT or_customer, COUNT(or_customer), SUM(or_qty) FROM orders GROUP BY or_customer
See GROUP BY for information about GROUP BY.
AVG
AVG(col)
This function returns the average of the values in the specified column. For example, the following returns the average or_qty value for rows in the orders table:
SELECT AVG(or_qty) FROM orders
The next example returns the or_qty averages for each customer (or_customer):
SELECT or_customer, AVG(or_qty) FROM orders GROUP BY or_customer
COUNT
COUNT(col)
This function returns the number of rows that don’t have null values in the specified column.
COUNT(*) returns a count of all the rows in a table that meet WHERE clause criteria (or a count of all rows if there is no WHERE clause).
The following statement returns a count of rows whose in_size value is greater than 5 and whose in_color value is not equal to null:
select count(in_color) from plants where in_size > 5
MAX
MAX(col)
This function returns the largest number in the specified column. For example, the following returns the largest or_qty value in the orders table:
SELECT MAX(in_itemid) FROM plants
The next example returns the largest or_qty value for each group of rows grouped by the or_customer code:
SELECT or_customer, MAX(or_qty) FROM orders GROUP BY or_customer
MAX is not automatically optimized, so there is no performance advantage if col is a primary key. For better performance when getting the maximum value of a key column, use TOP (which is optimized) and an ORDER BY clause with DESC. For example:
SELECT TOP 1 in_itemid FROM plants ORDER BY in_itemid DESC
MIN
MIN(col)
This function returns the smallest number in the specified column. For example, the following returns the smallest or_qty value in the orders table:
SELECT MIN(in_itemid) FROM plants
The next example returns the smallest or_qty value for each group of rows grouped by the or_customer code:
SELECT or_customer, MIN(or_qty) FROM orders GROUP BY or_customer
MIN is not automatically optimized, so there is no performance advantage if col is a primary key. For better performance when getting the minimum value of a key column, use TOP (which is optimized) and an ORDER BY clause. For example:
SELECT TOP 1 in_itemid FROM plants ORDER BY in_itemid
SUM
SUM(col)
This function returns the sum of the numbers in col, a column that contains numeric values. Null rows are ignored. For example, the following returns the sum of all or_qty values for the orders table:
SELECT SUM(or_qty) FROM orders
The next example returns the sum of or_qty values for each group of rows grouped by the or_customer code:
SELECT or_customer, SUM(or_qty) FROM orders GROUP BY or_customer
