Prev | SQL Engine Reference | Next |
GROUP BY
Remarks
In addition to the GROUP BY syntax in a SELECT statement as specified in the Microsoft ODBC Programmer's Reference, the Pervasive.SQL Engine supports an extended GROUP BY syntax that can include vendor strings.
A GROUP BY query returns a result set which contains one row of the select list for every group encountered. (See the Microsoft ODBC Programmer's Reference for the syntax of a select list.)
Examples
The following example uses the course table to produce a list of unique departments:
SELECT Dept_Name from Course GROUP BY Dept_Name
In the next example, the result set contains a list of unique departments and the number of courses in each department:
SELECT Dept_Name, COUNT(*) FROM Course GROUP BY Dept_NameNote that COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.
The rows operated on by the set function are those rows remaining after the WHERE search condition is applied. In this example, only those rows in the faculty table that have Salary > 80000 are counted:
SELECT COUNT(*) FROM Faculty WHERE Salary > 80000 GROUP BY Dept_NameThe following example shows an extended GROUP BY that includes vendor strings in an escape sequence. The Microsoft Web site provides additional information about escape sequences and vendor strings: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcescape_sequences_in_odbc.asp.
SELECT(--(*vendor(Microsoft), product(ODBC) fn left(at1.col2, 1) *)--) FROM at1 GROUP BY (--(*vendor(Microsoft), product(ODBC) fn left(at1.col2, 1) *)--) ORDER BY (--(*vendor(Microsoft), product(ODBC) fn left(at1.col2, 1) *)--) DESCSee Also
Prev GRANT |
Contents Up Check for Revisions | Next HAVING |