On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
