Using a GROUP BY with a Compound Query

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset