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

Mysql – Get sum of a column from three tables and know which table it came from

I have an odd question. I have three tables all with different information but a few common columns. I want to get a sum from the tables, but I also want to know which table the data came from. I can do the following and get my sum, but how to know which table it came from?

select 
    CustName, CustNumber, cost, sum(QUANTITY) as total 
from 
    (select CustName, CustNumber, cost, QUANTITY from table1 
     union all
     select CustName, CustNumber, cost, QUANTITY from table2 
     union all
     select CustName, CustNumber, cost, QUANTITY from table3) x
     
WHERE CustNumber = '894047001034' AND CustName = 'Joseph Smythe'
group by CustName;
Tags:

2

Answers


  1. 0

    This is untested code so some tweaking might be needed.

    You could add the tablename as an extra column from your queries and then add the table name to your outer select;

    select 
    CustName, CustNumber, cost, sum(QUANTITY) as total, table_name
from 
    (select CustName, CustNumber, cost, QUANTITY, "table1" as table_name from table1 
     union all
     select CustName, CustNumber, cost, QUANTITY, "table2" as table_name from table2 
     union all
     select CustName, CustNumber, cost, QUANTITY, "table3" as table_name from table3) x
     
WHERE CustNumber = '894047001034' AND CustName = 'Joseph Smythe'
group by CustName;
    Login or Signup to reply.
  2. 0 
    SELECT
CustName, CustNumber, cost, sum(QUANTITY) as total,
SUM(CASE WHEN table_name = "table1" THEN QUANTITY ELSE 0 END) as total1,
SUM(CASE WHEN table_name = "table2" THEN QUANTITY ELSE 0 END) as total2,
SUM(CASE WHEN table_name = "table3" THEN QUANTITY ELSE 0 END) as total3
FROM
(
   SELECT CustName, CustNumber, cost, QUANTITY, "table1" as table_name 
   FROM table1 
   union all
   SELECT CustName, CustNumber, cost, QUANTITY, "table2" as table_name 
   FROM table2
   union all
   SELECT CustName, CustNumber, cost, QUANTITY, "table3" as table_name 
) x
WHERE CustNumber = '894047001034' AND CustName = 'Joseph Smythe'
GROUP BY CustName, CustNumber, cost;

    Or just change the group by of the previous nice suggestion with
    GROUP BY CustName, CustNumber, cost, table_name

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search