Question 231117 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – select query from 3 tables using sum()

I have 3 mysql tables. All the tables will have many entries of the same person.
For desire output need help

Table_1                               Table_2                             Table_3

id   name   bill                  id   name   total                  id   name   total                 
1    John   100                   1    John   20                     1    John   100
2    Kevin  50                    2    Tom    30                     2    Tom    20 
3    Tom    50                                                       3    Tom    30 
4    John   20



Output_Table
name   sum(bill)   sum(total)   sum(amount)
John   120         20           100
Kevin  50          null         null
Tom    50          30           50
Tags:

2

Answers


  1. 0

    Solution could be as below query

    SELECT SUM(t1.bill) bill, sum(t2.total) total, sum(t3.total) amount  FROM Table_1 t1
LEFT JOIN Table_2 t2 on t2.name = t1.name
LEFT JOIN Table_3 t3 on t3.name = t1.name 
GROUP BY t1.name
    Login or Signup to reply.
  2. 0

    This is the test I did in dbfiddle.uk. I think we can’t determine which table contains all the names, for example, table_1 has Kevin, other tables don’t, table_2 has Peter, other tables don’t, so we need to look up all the names jointly, and then sum the data according to the name.

    My sql looks bloated and complicated, but I hope it helps.

    CREATE TABLE table_1 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  bill INT
);

CREATE TABLE table_2 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  total INT
);

CREATE TABLE table_3 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  amount INT
);

insert into table_1 values
(1,'John',100),
(2,'Kevin ',50),
(3,'Tom',50),
(4,'John',20);

insert into table_2 values
(1,'John',20),
(2,'Tom',30),
(3,'Peter', 70);


insert into table_3 values
(1,'John',100),
(2,'Tom',20),
(3,'Tom',30);

    SELECT
    DISTINCT name,
    (SELECT SUM(bill) FROM table_1 WHERE table_1.name = names.name) AS sum_bill,
    (SELECT SUM(total) FROM table_2 WHERE table_2.name = names.name) AS sum_total,
    (SELECT SUM(amount) FROM table_3 WHERE table_3.name = names.name) AS sum_amount
FROM
    (
        SELECT name FROM table_1
        UNION
        SELECT name FROM table_2
        UNION
        SELECT name FROM table_3
    ) AS names;
    name sum_bill sum_total sum_amount
    John 120 20 100
    Kevin 50 null null
    Tom 50 30 50
    Peter null 70 null
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search