CODEWITHSUNDEEP
mysqlsql
user6100679
user6100679

Reputation:

Joining two tables and adding column values together

I have two tables, in both tables I have a unique column titled phone_number and then a column called spring with 3 others columns. The spring column has a number value. This table has about 3000 rows. I have a duplicate table with the same information but with only about 300 rows. I want to combine the values of both tables' spring columns based on the phone_number unique column.

I have tried doing a MERGE and also a UNION but I don't really understand how they work and kept getting syntax errors.

SELECT
    accountstwo.phone_number, accountstwo.deposit_total, accountstwo.summer, accountstwo.total_remain,
    (
        SUM(accountstwo.spring) + SUM(accountstwonu.spring)
    ) spring
FROM accountstwo LEFT JOIN
     accountstwonu
     ON accountstwonu.phone_number = accountstwo.phone_number
GROUP BY phone_number;

I can get the tables to join but it creates a new column called spring with only the combined column totals and the original table's other 2700 rows return as NULL. I want to keep the 2700 rows data as well as combine the other 300.

Upvotes: 0

Views: 1256

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31772

in both tables I have a unique column titled phone_number

If phone_number is unique in both tables, then GROUP BY and SUM() make no sense, since the (LEFT) JOIN result can only have one row per phone number. All you need is probably COALESCE() for the right table, to convert NULL to zero:

SELECT
    accountstwo.phone_number,
    accountstwo.deposit_total,
    accountstwo.summer, accountstwo.total_remain,
    accountstwo.spring + COALESCE(accountstwonu.spring, 0) as spring
FROM accountstwo LEFT JOIN
     accountstwonu
     ON accountstwonu.phone_number = accountstwo.phone_number;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Is this what you want?

SELECT a2.phone_number, a2.deposit_total, a2.summer, a2.total_remain,
       (COALESCE(a2.spring, 0) + COALESCE(SUM(a2nu.spring), 0)) as spring
FROM accountstwo a2 LEFT JOIN
     accountstwonu a2nu
     ON a2.phone_number = a2nu.phone_number
GROUP BY a2.phone_number, a2.deposit_total, a2.summer, a2.total_remain, a2.spring;

Upvotes: 1

Related Questions