Postgres query for matrix table

MySQL...

It's a challenge. I will walk through the 2 steps needed:

First, let's build (and debug) a query that lists all the caller-called pairs, some of them duplicated. (We'll count them later.)

SELECT ger.name AS er_name,
       ged.name AS ed_name
FROM Call_details AS cd
JOIN Target_groups_map AS mer  ON mer.targets = cd.caller
JOIN Target_groups_map AS med  ON med.targets = cd.called
JOIN Target_groups AS ger ON ger.id = mer.target_groups
JOIN Target_groups AS ged ON ged.id = med.target_groups;

Second, let's do the pivot, count the dups, default to '-', etc:

SELECT
er_name                               AS '',
IFNULL(SUM(ed_name = 'Group 1'), '-') AS 'Group 1',
IFNULL(SUM(ed_name = 'Group 2'), '-') AS 'Group 2',
IFNULL(SUM(ed_name = 'Group 3'), '-') AS 'Group 3',
IFNULL(SUM(ed_name = 'Group 4'), '-') AS 'Group 4'
FROM ( ... ) AS y
GROUP BY er_name;

For the '...' put the entire query from the first step (don't include the ;').

The SUM may seem strange, but here's how that works: The boolean expression inside it turns into 0 (false) or 1 (true), then SUM effectively counts.

If the number of Groups is not exactly 4, then you should abandon trying to do this in SQL. Sure, it could be done with a really convoluted stored procedure to construct the second query, but that makes my brain hurt.

Whenever I do pivoting, I write the code in the client language, such as PHP.

This sql statement:

SELECT 
  tg1.name as caller, 
  tg2.name as called, 
  SUM(cd.caller IS NOT NULL AND cd.called IS NOT NULL) as cnt 
FROM 
  Target_groups tg1 JOIN Target_groups tg2   
  LEFT JOIN 
    Target_groups_map tgm1 ON tg1.id=tgm1.target_groups
  LEFT JOIN 
    Target_groups_map tgm2 ON tg2.id=tgm2.target_groups
  LEFT JOIN
    Call_details cd ON tgm1.targets=cd.caller AND tgm2.targets=cd.called
GROUP BY
  tg1.id,tg2.id;

Gives:

+---------+---------+------+
| caller  | called  | cnt  |
+---------+---------+------+
| Group 1 | Group 1 |    0 |
| Group 1 | Group 2 |    1 |
| Group 1 | Group 3 |    0 |
| Group 1 | Group 4 |    2 |
| Group 2 | Group 1 |    1 |
| Group 2 | Group 2 |    0 |
| Group 2 | Group 3 |    0 |
| Group 2 | Group 4 |    1 |
| Group 3 | Group 1 |    1 |
| Group 3 | Group 2 |    0 |
| Group 3 | Group 3 |    1 |
| Group 3 | Group 4 |    1 |
| Group 4 | Group 1 |    0 |
| Group 4 | Group 2 |    0 |
| Group 4 | Group 3 |    0 |
| Group 4 | Group 4 |    0 |
+---------+---------+------+

But if you want formatted EXACTLY how you have it printed out you'll need a prepared sql statement and then do what Mr Rick James says, if you need it dynamic (dont know the Group names beforehand) here is a starting point:

SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT(CONCAT('"" as ',quote(name)))) INTO @sql FROM Target_groups; SET @sql = CONCAT("SELECT Target_groups.name, ", @sql, " FROM Target_groups"); PREPARE stmt FROM @sql; EXECUTE stmt;

Statement prepared

+---------+---------+---------+---------+---------+
| name    | Group 1 | Group 2 | Group 3 | Group 4 |
+---------+---------+---------+---------+---------+
| Group 1 |         |         |         |         |
| Group 2 |         |         |         |         |
| Group 3 |         |         |         |         |
| Group 4 |         |         |         |         |
+---------+---------+---------+---------+---------+

ps - this is to help anyone trying to solve this + test:

create table Targets (id int, name bigint) engine=innodb;
insert into Targets values (1,9999999991),(2,9999999992),(3,9999999993),(4,9999999994),(5,9999999995),(6,9999999996),(7,9999999997),(8,9999999998);

create table Target_groups (id int, name varchar(16)) engine=innodb;
insert into Target_groups values (1,'Group 1'),(2,'Group 2'),(3,'Group 3'),(4,'Group 4');

create table Target_groups_map (id int,targets bigint,target_groups int) engine=innodb;
insert into Target_groups_map values (1,9999999991,1),(2,9999999992,1),(3,9999999993,2),(4,9999999994,2),(5,9999999995,3),(6,9999999996,3),(6,9999999997,4),(6,9999999998,4);

create table Call_details (id int,caller bigint,called bigint) engine=innodb;
insert into Call_details values (1,9999999995,9999999996),(2,9999999992,9999999998),(3,9999999993,9999999998),(4,9999999994,9999999991),(5,9999999995,9999999998),(6,9999999996,9999999992),(6,9999999991,9999999993),(6,9999999992,9999999998);

I made a query:

Select A.name as caller,A.ToGroupName as called,Count(phone_number) as count
From (
                Select G.id,T.phone_number,G.Name,FC.Called,TG.name as ToGroupName
                From targets T
                LEFT Join target_groups_map GM on GM.targets = T.phone_number
                Left Join target_groups G on G.id = GM.target_groups
                INNER Join call_details FC on FC.Caller = T.phone_number
                INNER Join target_groups_map TGM on TGM.targets = FC.called
                Inner Join target_groups TG on TG.id = TGM.target_groups
) A
Group By A.name,A.ToGroupName
Order By A.name,A.ToGroupName

Gives output:

+---------+---------+------+
| caller  | called  | cnt  |
+---------+---------+------+
| Group 1 | Group 2 |    1 |
| Group 1 | Group 4 |    3 |
| Group 1 | Group 5 |    2 |
| Group 2 | Group 1 |    2 |
| Group 2 | Group 4 |    1 |
| Group 2 | Group 5 |    3 |
| Group 3 | Group 1 |    1 |
| Group 3 | Group 3 |    1 |
| Group 3 | Group 4 |    1 |
| Group 3 | Group 5 |    1 |
| Group 4 | Group 2 |    1 |
| Group 5 | Group 2 |    2 |
+---------+---------+------+

converted it into matrix format