Re: Outer Join help please

On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<rory(at)campbell-lange(dot)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