JOIN Call table to grouping by Origination and Destination It's a call system.

  tables structure and data sample : 

tbl_countries

CREATE TABLE tbl_countries
(
    country_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    country varchar(100) NOT NULL
);
INSERT INTO tbl_countries (country) VALUES ('Austria');
INSERT INTO tbl_countries (country) VALUES ('Brazil');
INSERT INTO tbl_countries (country) VALUES ('Denmark');
INSERT INTO tbl_countries (country) VALUES ('France');
INSERT INTO tbl_countries (country) VALUES ('Lebanon');


tbl_destination_clients

CREATE TABLE tbl_destination_clients
(
    des_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    des_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_destination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_destination_clients (country_id);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('961123456', 5);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('55123456', 2);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('45123456', 3);


tbl_origination_clients

CREATE TABLE tbl_origination_clients
(
    orig_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_origination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_origination_clients (country_id);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('33123456', 4);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('43123456', 1);


tbl_calls

CREATE TABLE tbl_calls
(
    call_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client_id int(11) NOT NULL,
    des_client_id int(11) NOT NULL,
    call_duration int(11) NOT NULL,
    CONSTRAINT tbl_calls_ibfk_1 FOREIGN KEY (orig_client_id) REFERENCES tbl_origination_clients (orig_client_id),
    CONSTRAINT tbl_calls_ibfk_2 FOREIGN KEY (des_client_id) REFERENCES tbl_destination_clients (des_client_id)
);
CREATE INDEX des_client_id ON tbl_calls (des_client_id);
CREATE INDEX orig_client_id ON tbl_calls (orig_client_id);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 5);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 6);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (2, 4, 9);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 1, 10);


 Attemps :

I try to use JOIN between the same table tbl_calls, like this :

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c 
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.des_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by   
  co.country,
  cd.country;


But it's not working ;(

The Result of this sample data must be :

Origination  Destination  SUM(call_duration)
------------+------------+----------------
France       Brazil       11 -&gt; (5+6)
Austria      Denmark      9
France       Lebanon      10


Thanks ..
I think you need to change one join column on the inner join from tbl_calls to  tbl_origination_clients. You have currently c.des_client_id but it should be c.orig_client_id.

Query with adapted join column

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c 
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.orig_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by   
  co.country,
  cd.country;


Result

| Origination | Destination | TotalDuration |
| ----------- | ----------- | ------------- |
| Austria     | Denmark     | 9             |
| France      | Brazil      | 11            |
| France      | Lebanon     | 10            |


DB-Fiddle