CODEWITHSUNDEEP
sqlpostgresql
codeaperature
codeaperature

Reputation: 1118

To use a self join, union or some other operation(s) on an SQL table with bi-direction graph information

If I have a table:

 talker | listener | minutes 
--------+----------+---------
 sue    | rob      |       3
 sue    | jan      |       2
 jan    | sue      |       1
 rob    | sue      |       7
 jan    | sue      |       4
 rob    | sue      |       6
 rob    | jan      |       5
 dan    | jan      |       5
 sue    | abe      |       9

What is the best way to determine the total conversation time for each person? Example:

name | minutes
-----+--------
 dan |       5
 abe |       9
 rob |      21
 sue |      32
 jan |      17

(Assume I added correctly.)

Also, I'd like to know if it's possible to make the initial table double in size with a self join or other mechanics. (Goal being to make a table with select talker as name, minutes unioned with select listener as name, minutes).

Some help:

create table discussions(talker varchar(10), listener varchar(10), minutes INT);
insert into discussions(talker, listener, minutes) values ('sue', 'rob', 3);
insert into discussions(talker, listener, minutes) values ('sue', 'jan', 2);
insert into discussions(talker, listener, minutes) values ('jan', 'sue',  1);
insert into discussions(talker, listener, minutes) values ('rob', 'sue',  7);
insert into discussions(talker, listener, minutes) values ('jan', 'sue',  4);
insert into discussions(talker, listener, minutes) values ('rob', 'sue',  6);
insert into discussions(talker, listener, minutes) values ('rob', 'jan',  5);
insert into discussions(talker, listener, minutes) values ('dan', 'jan',  5);
insert into discussions(talker, listener, minutes) values ('sue', 'abe',  9);

Upvotes: 0

Views: 61

Answers (3)

Rajat
Rajat

Reputation: 5803

Slightly different logic using CTE. It most likely will perform worse than the accepted answer, but I am leaving it in here so you can test it yourself--either as is or rewriting it using a temp table or a subquery.

with cte (name, minutes) as

(select talker, sum(minutes)
 from discussions
 group by talker
 union all
 select listener, sum(minutes)
 from discussions
 group by listener)
  
select name, sum(minutes)
from cte
group by name;

Upvotes: 0

Atif
Atif

Reputation: 2210

Using join method using can achieve the same result:

     SELECT (COALESCE(TALKER_MINS, 0) + COALESCE(LISTENER_MINS, 0)) AS MINUTES, COALESCE(X.TALKER, Y.LISTENER) as NAME FROM (
SELECT SUM(MINUTES) TALKER_MINS, A.TALKER FROM discussions A GROUP BY TALKER) X 
FULL OUTER JOIN
(SELECT SUM(MINUTES) LISTENER_MINS, A.listener FROM discussions A GROUP BY listener) Y
ON X.TALKER = Y.LISTENER ORDER BY 1;

Upvotes: 0

juergen d
juergen d

Reputation: 204884

select name, sum(minutes)
from
(
  select talker as name, minutes from discussions
  union all 
  select listener, minutes from discussions
) tmp
group by name

Upvotes: 3

Related Questions