The problem at hand is to select the customers delinquent on their payments. So we want to select the customers where the customers' purchases are 30 days old. The customer table and the purchases tables are what we want.

The query that should be in your mind first off follows. Unfortunately, the best query does not always come to mind first.

	SELECT name, address 
	FROM customers c JOIN purchases ON (purchaser_id = customer_id)
	WHERE purchase_date > current_date-30;

This query does the same thing and gives the same results. But is unnecessarily ugly, complicated and slow.

	SELECT name, address 
	FROM customers 
	where EXISTS (SELECT 1 
		FROM purchases 
		WHERE purchaser_id=customers.id AND purchase_date > current_date-30 
		LIMIT 1);

A variation on the above query uses IN.

	SELECT name, address 
	FROM customers 
	WHERE id IN (SELECT purchaser_id 
		FROM purchases 
		WHERE purchase_date > current_date-30 LIMIT 1);

When constructing a SELECT statement, first build the set of data that you want to return as well as the data you want to qualify on. Use the JOIN statement to bring two (or more) tables together to construct that set. The target list will be the data you want to return and the condition should identify the primary condition of the query.

Contributors: elein at varlena.com andrewsn at freenode.net

Constructing Aggregates Constructing Aggregates 26-Nov-2005

In my family, we are mostly women, kids and pets. This is a picture of the family tree from my mother with three daughters and the sisters' kids and all of our pets. The people are blue the pets are brown. (Names have been changed to protect the innocent :)

The tables we have are these:

What I want to know is how may kids and pets each person has. So that query is easy.

	SELECT people.login, COUNT(pets.name) AS pets, COUNT(kids.name) AS kids
	FROM people JOIN pets ON (login=owner) JOIN kids ON (login = parent1)
	GROUP BY people.login;
	   login   | pets | kids
	-----------+------+------
	 ann       |    3 |    3
	 rachel    |    3 |    3
	 cora      |    1 |    1
	 stephanie |    1 |    1
	(4 rows)

	SELECT people.login, COUNT(pets.name) AS pets, COUNT(kids.name) AS kids
	FROM people
	LEFT OUTER JOIN pets ON (login=owner) LEFT OUTER JOIN kids ON (login = parent1)
	group by people.login;
	   login   | pets | kids
	-----------+------+------
	 ann       |    3 |    3
	 cat       |    1 |    0
	 CJ        |    0 |    0
	 cora      |    1 |    1
	 david     |    0 |    0
	 elein     |    4 |    0
	 laura     |    0 |    0
	 rachel    |    3 |    3
	 stephanie |    1 |    1
	(9 rows)

	SELECT login, count(pets.name )
	FROM people LEFT OUTER JOIN pets ON (login=owner)
	GROUP BY login;
	   login   | count
	-----------+-------
	 ann       |     1
	 cat       |     1
	 CJ        |     0
	 cora      |     1
	 david     |     0
	 elein     |     4
	 laura     |     0
	 rachel    |     1
	 stephanie |     1
	
	SELECT login, COUNT(kids.name )
	FROM people LEFT OUTER JOIN kids ON (login=parent1)
	GROUP BY login;
	   login   | count
	-----------+-------
	 ann       |     3
	 cat       |     0
	 CJ        |     0
	 cora      |     1
	 david     |     0
	 elein     |     0
	 laura     |     0
	 rachel    |     3
	 stephanie |     1

	SELECT login, pets.name AS pet, kids.name AS kid
	FROM people LEFT OUTER JOIN pets on (login=owner)    
	LEFT OUTER JOIN kids ON (login = parent1);
	   login   |    pet    |    kid
	-----------+-----------+-----------
	 ann       | buster    | elein
	 ann       | buster    | rachel
	 ann       | buster    | stephanie
	 cat       | edward    |
	 CJ        |           |
	 cora      | bowwow    | CJ
	 david     |           |
	 elein     | geordie   |
	 elein     | belle     |
	 elein     | bubbajack |
	 elein     | leaf      |
	 laura     |           |
	 rachel    | kiwi      | cat
	 rachel    | kiwi      | laura
	 rachel    | kiwi      | david
	 stephanie | chewy     | cora
	(16 rows)

Because there is no correlation between pets and kids we will have to JOIN them to people separately. Abandoning the JOIN syntax for the where clause we join in the people table twice:

	SELECT p1.login, COUNT(k.name) AS kids, COUNT(pt.name) AS pets
	FROM kids k , pets pt, people p1, people p2
	WHERE k.parent1 = p1.login AND pt.owner = p2.login
	AND p1.login = p2.login
	GROUP BY p1.login;
	   login   | kids | pets
	-----------+------+------
	 ann       |    3 |    3
	 rachel    |    3 |    3
	 cora      |    1 |    1
	 stephanie |    1 |    1
	(4 rows)

	SELECT login, kids, pets
	FROM
	   (SELECT login, COUNT(kids.name) AS kids
	   FROM people LEFT OUTER JOIN kids ON (login=parent1)
	   group by login) k
	JOIN
	   (SELECT login, COUNT(pets.name) AS pets
	   FROM people LEFT OUTER JOIN pets ON (login=owner)
	   GROUP BY login ) p 
	USING (login);
	   login   | kids | pets
	-----------+------+------
	 ann       |    3 |    1
	 cat       |    0 |    1
	 CJ        |    0 |    0
	 cora      |    1 |    1
	 david     |    0 |    0
	 elein     |    0 |    4
	 laura     |    0 |    0
	 rachel    |    3 |    1
	 stephanie |    1 |    1
	(9 rows)

Test your aggregates, especially if you are using multiple aggregates in one statement. More often than not, aggregates on different things cannot be done without subselects. Use subselects sparingly, but you will find them invaluable for statements with multiple aggregates.

Contributors: elein at varlena.com