Crafting an SQL query to return all of the rows of one table and some aggregate value in another table that is associated with those rows is simplified by using a derived table. For this example we'll use a table of Customers and a table of their Purchases.

Here is the sample data in our Customers table:

+---------+-------+
| cust_id | name  |
+---------+-------+
|       1 | tom   |
|       2 | mary  |
|       3 | fred  |
|       4 | jane  |
|       5 | mike  |
|       6 | alice |
|       7 | walt  |
+---------+-------+

Here is the sample data in our Purchases table:

+---------+-------------+--------+
| cust_id | purchase_dt | amount |
+---------+-------------+--------+
|       1 | 2011-08-01  |  92.05 |
|       1 | 2011-08-11  |  24.67 |
|       1 | 2011-08-06  | 101.55 |
|       3 | 2011-08-07  |  66.33 |
|       3 | 2011-08-11  |  57.34 |
|       3 | 2011-08-14  |  88.49 |
|       5 | 2011-08-16  |  12.27 |
|       5 | 2011-08-19  | 245.21 |
|       7 | 2011-08-21  | 154.87 |
|       7 | 2011-08-20  |  86.11 |
+---------+-------------+--------+

Note that only 4 of our 7 Customers have recorded Purchases, and that 2 of those Customers have 3 Purchases each, while the other 2 have just 2 each.

We're going to use a LEFT JOIN in our SQL query because that will return all of the rows in the first-named table and any matching rows from the second table. For those rows in the first-named table that have no match in the second table (no Purchases, in our example) a NULL result is returned. A LEFT JOIN can be visualized in terms of a Venn Diagram:

Our SQL query looks like this:

SELECT t1.cust_id,
       t1.name,
       IFNULL(t2.cnt_purchases, 0) AS cnt_purchases,
       IFNULL(t2.sum_purchases, 0) AS sum_purchases
FROM customers AS t1
       LEFT JOIN (SELECT cust_id,
                         COUNT(*) AS cnt_purchases,
                         SUM(amount) AS sum_purchases
                  FROM purchases 
                  GROUP BY cust_id) AS t2
       ON t1.cust_id = t2.cust_id
ORDER  BY t2.sum_purchases DESC;

The IFNULL() function subtitutes a zero for a NULL value and is for display purposes only. The derived table is essentially a subquery that has been given the alias t2.

And our results look like this:

+---------+-------+---------------+---------------+
| cust_id | name  | cnt_purchases | sum_purchases |
+---------+-------+---------------+---------------+
|       5 | mike  |             2 |        257.48 |
|       7 | walt  |             2 |        240.98 |
|       1 | tom   |             3 |        218.27 |
|       3 | fred  |             3 |        212.16 |
|       2 | mary  |             0 |          0.00 |
|       6 | alice |             0 |          0.00 |
|       4 | jane  |             0 |          0.00 |
+---------+-------+---------------+---------------+

Collapse this tip

McArthur Web Services

Data Management And Visualization

Adrift In A Sea Of Information That Defies Comprehension?

Leverage Your Data For A Competitive Edge!

Tips & Tidbits