In addition to the GROUP BY syntax in a SELECT statement.
A GROUP BY query returns a result set which contains one row of the select list for every group encountered.
The use of aliases is allowed within the GROUP BY clause. The alias must differ from any column names within the table.
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