Sams Teach Yourself SQL in 10 Minutes, Second Edition

Using Subqueries As Calculated Fields

Another way to use subqueries is in creating calculated fields. Suppose you want to display the total number of orders placed by every customer in your Customers table. Orders are stored in the Orders table along with the appropriate customer ID.

To perform this operation, follow these steps:

1.	Retrieve the list of customers from the Customers table.
2.	For each customer retrieved, count the number of associated orders in the Orders table.

As you learned in the previous two lessons, you can use SELECT COUNT(*) to count rows in a table, and by providing a WHERE clause to filter a specific customer ID, you can count just that customer's orders. For example, the following code counts the number of orders placed ...