GROUP BY

In addition to the GROUP BY syntax in a SELECT statement, Pervasive PSQL 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. You can also find additional information about escape sequences and vendor strings on the Microsoft Web site.

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