I'm having troube doing a left outer self join on a table. The sent
column shows the number of items sent to each recipient from each
source. The received column (generated by the outer join) is incorrect
because although it is summing the number of messages by recipient,
these need to be filtered by source too.

How can I do a join on two columns in the master table?

Thanks for any help.
Rory


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



----------------------- function definition -----------------------


DROP TYPE dlr_report CASCADE;

CREATE TYPE dlr_report as (
recipient VARCHAR,
source VARCHAR,
sent INTEGER,
received INTEGER,
outstanding INTEGER
);


CREATE OR REPLACE FUNCTION report_on_dlr ()
RETURNS SETOF dlr_report
AS '
DECLARE
resulter dlr_report%rowtype;
BEGIN

FOR resulter IN
SELECT
dd.t_to as recipient,
dd.t_from as source,
count(dd.id) as sent,
CASE
WHEN received_ok is NULL THEN 0
ELSE received_ok
END as received,
count(dd.id) - received_ok as outstanding
FROM
dlr dd
LEFT OUTER JOIN (
SELECT
t_to as target, count(id) as received_ok
FROM
dlr
WHERE
dlr = 1 and t_from = source
GROUP BY
target
) AS ok
ON t_to = ok.target
GROUP BY
dd.t_to, dd.t_from, received, received_ok
ORDER BY
dd.t_to, dd.t_from
LOOP

RETURN NEXT
resulter;

END LOOP;

RETURN;

END;'
LANGUAGE plpgsql;

------------------------- data definition -------------------------

CREATE TABLE dlr (
id serial NOT NULL,
t_to character varying(30),
t_from character varying(30),
dlr smallint
);

COPY dlr (id, t_to, t_from, dlr) FROM stdin;
1 22 1 \N
2 22 1 1
3 22 1 1
4 23 1 1
5 25 1 1
6 25 2 1
7 26 2 \N
8 26 2 0
9 27 2 0
10 27 2 0
11 27 2 0
\.

SELECT pg_catalog.setval ('dlr_id_seq', 11, true);



--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:[color=blue]
>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.[/color]

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