Using GROUP BY with a Compound Query

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 15.  Combining Multiple Queries into One


Using GROUP BY with a Compound Query

Unlike ORDER BY, 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 :

 graphics/syntax_icon.gif 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.

 graphics/mysql_icon.gif graphics/input_icon.gif  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;  graphics/output_icon.gif 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:

 graphics/mysql_icon.gif graphics/input_icon.gif  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;  graphics/output_icon.gif 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.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net