Unlike the ORDER BY, the GROUP BY can be used in each SELECT statement of a compound query, but also can be used following all individual queries. In addition, the HAVING clause (sometimes used with the GROUP BY clause) can be used in each SELECT statement of a compound statement.
The syntax is as follows:
SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] [ GROUP BY ] [ HAVING ] OPERATOR {UNION | EXCEPT | INTERSECT | UNION ALL} SELECT COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE ] [ GROUP BY ] [ HAVING ] [ ORDER BY ]
In the following example, you select a literal string to represent customer records, employee records, and product records. Each individual query is simply a count of all records in each appropriate table. The GROUP BY clause is used to group the results of the entire report by the numeric value 1, which represents the first column in each individual query.
SELECT 'CUSTOMERS' TYPE, COUNT(*) FROM CUSTOMER_TBL UNION SELECT 'EMPLOYEES' TYPE, COUNT(*) FROM EMPLOYEE_TBL UNION SELECT 'PRODUCTS' TYPE, COUNT(*) FROM PRODUCTS_TBL GROUP BY 1;
TYPE COUNT(*) ----------- -------- CUSTOMERS 15 EMPLOYEES 6 PRODUCTS 9 3 rows selected.
The following query is identical to the previous query, except that the ORDER BY clause is used as well:
SELECT 'CUSTOMERS' TYPE, COUNT(*) FROM CUSTOMER_TBL UNION SELECT 'EMPLOYEES' TYPE, COUNT(*) FROM EMPLOYEE_TBL UNION SELECT 'PRODUCTS' TYPE, COUNT(*) FROM PRODUCTS_TBL GROUP BY 1 ORDER BY 2;
TYPE COUNT(*) ----------- -------- EMPLOYEES 6 PRODUCTS 9 CUSTOMERS 15 3 rows selected.
This is sorted by column 2, which was the count on each table. Hence, the final output is sorted by the count from least to greatest.