Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
  • Hour 15. Combining Multiple Queries into One
    • Single Queries Versus Compound Queries
    • Why Would I Ever Want to Use a Compound Query?
    • Compound Query Operators
    • Using an ORDER BY with a Compound Query
    • Using GROUP BY with a Compound Query
    • Retrieving Accurate Data
    • Summary
    • Workshop
    • Q&A
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

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:

   
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.

Share This