CODEWITHSUNDEEP
mysqlsql
tomish
tomish

Reputation: 77

Add up values for employees in three mysql tables

I have a very simple problem I am trying to solve but cannot wrap my head around it.

I have three tables of identical structure

t1.id, t1.cust_id, t1.name, t1.value
t2.id, t2.cust_id, t2.name, t2.value
t3.id, t3.cust_id, t3.name, t3.value

Customers appear in some tables but not in others; the 'value' record in each is a dollar amount.

I would like to run a query in mySQL that produces a summation table that adds up all the purchases made by each customer in the three tables.

My desired output would look something like:

 Name        Customer ID      T1      T2    T3

  Joe           88888        12.45  45.90  2.34
  Ted           99999         8.90   3.45  null
  Sue           12123         9.45   2.45  null

I've tried a few queries with JOINs but with no satisfactory results.

Thanks your help!

Upvotes: 0

Views: 28

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30839

You can do it with SELECT, e.g.:

    SELECT (
        (SELECT COALESCE(SUM(value),0) FROM t1 WHERE cust_id = 100) 
        +
        (SELECT COALESCE(SUM(value),0) FROM t2 WHERE cust_id = 100)
        +
        (SELECT COALESCE(SUM(value),0) FROM t3 WHERE cust_id = 100)
    ) as total;

Here's the SQL Fiddle.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use union all to combine the rows from 3 tables and then use aggregation.

select cust_id,name,sum(t1val),sum(t2val),sum(t3val)
from (
select id, cust_id, name, value as t1val, null as t2val, null as t3val from t1
union all
select id, cust_id, name, null, value, null from t2
union all
select id, cust_id, name, null, null ,value from t3
) t
group by cust_id,name

Upvotes: 1

Related Questions