Re: Outer Join help please

On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
>The sent
>column shows the number of items sent to each recipient from each
>source. The received column [...] is summing the number of messages
> by recipient [and] need to be filtered by source too.

SELECT t_to AS recipient,
       t_from AS sender,
       count(*) AS sent,
       sum(CASE WHEN dlr = 1 THEN 1 ELSE 0 END) AS received,
       sum(CASE WHEN dlr = 1 THEN 0 ELSE 1 END) AS outstanding
  FROM dlr
 GROUP BY t_to, t_from;

gives

 recipient | sender | sent | received | outstanding
-----------+--------+------+----------+-------------
 22        | 1      |    3 |        2 |           1
 23        | 1      |    1 |        1 |           0
 25        | 1      |    1 |        1 |           0
 25        | 2      |    1 |        1 |           0
 26        | 2      |    2 |        0 |           2
 27        | 2      |    3 |        0 |           3
(6 rows)

but I'm not sure whether this is what you want.  I didn't even use a
join ...

If it meets your requirements and you are sure that dlr is always 0,
1, or NULL, then here is a simpler version:

SELECT t_to AS recipient,
       t_from AS sender,
       count(*) AS sent,
       sum(dlr) AS received,
       count(*) - sum(dlr) AS outstanding
  FROM dlr
 GROUP BY t_to, t_from;

Servus
 Manfred