Subqueries in a Projection clause

A subquery can occur in the Projection clause of another SELECT statement. The following query shows how you might use a subquery in a Projection clause to return the total shipping charges (from the orders table) for each customer in the customer table. You could also write this query as a join between two tables.

Figure 1. Query

SELECT customer.customer_num,
   (SELECT SUM(ship_charge) 
       FROM orders
       WHERE customer.customer_num = orders.customer_num) 
         AS total_ship_chg
   FROM customer;

Figure 2. Query result

customer_num total_ship_chg

         101         $15.30
         102
         103
         104         $38.00
         105
        ⋮
         123          $8.50
         124         $12.00
         125
         126         $13.00
         127         $18.00
         128