GROUP BY

Remarks

In addition to the GROUP BY syntax in a SELECT statement, Pervasive.SQL 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.)

The use of aliases is allowed within the GROUP BY clause. The alias must differ from any column names within the table.

Examples

The following example uses the course table to produce a list of unique departments:

SELECT Dept_Name from Course GROUP BY Dept_Name 

This same example could use an alias, in this case "dn," to produce the same result:

SELECT Dept_Name dn from Course GROUP BY dn 

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_Name 

Note 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_Name 

The 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) *)--) DESC 

See Also

SELECT