combine two columns in 1 column mysql

Question

i have tablea and table b

tablea :

Nama    Jumlah
A       66
B       95
C       47
E       57
F       52

tableb:

Nama    Jumlah  Gaji
A       35      47
B       28      51
C       18      24
D       27      30
E       30      29
G       31      16

how to make query that can combine two tables in one table an to be like this

result :

Nama    Jumlah  Gaji
A       101     47
B       123     51
C       65      24
D       27      30
E       87      29
F       52      0
G       31      16

it's my query. but i can't get the the result like that.

SELECT a.nama, (a.jumlahtotala+b.jumlahtotalb) AS Jumlah FROM (SELECT nama, SUM(jumlah) AS jumlahtotala FROM tablea GROUP BY nama) a JOIN (SELECT nama, SUM(jumlah) AS jumlahtotalb, SUM(gaji) AS gaji FROM tableb GROUP BY nama) b GROUP BY a.name

thanks for your help

EDITED
Sorry for another question in comment

SMA · Accepted Answer · 2015-01-31 06:42:23Z

2

Try joining the two tables like:

SELECT b.Nama, IFNULL(a.Jumlah, 0) + b.Jumlah, b.Gaji
FROM tablea a RIGHT JOIN tableb b
ON a.Nama = b.Nama

edited Jan 31, 2015 at 6:42

answered Jan 31, 2015 at 6:18

SMA

37.2k8 gold badges53 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jun Rikson Over a year ago

A bit wrong field name at Jumlah, must be ISNULL(a.Jumlah, 0) + b.Jumlah

SMA Over a year ago

woops typo! Thanks @ColourDalnet

Muhammad Jody Over a year ago

i got an error #1582 - Incorrect parameter count in the call to native function 'ISNULL'

Muhammad Jody Over a year ago

thanks @almas shaikh. but i have another question. if tablea has E row, but tableb has no E row. see my new comment below thanks

spencer7593 Over a year ago

This query depends on the assumption that Nama column is unique in each of the two tables. (It is unique in the example data, the assumption may turn out to be true, but assumptions based on the extrapolation of limited sample data is dangerous.) This query also assumes that the result should exclude rows that exist in tablea that don't have a matching row in tableb. That may also be a valid assumption. But again, that assumption may be based on extrapolating limited sample data.

spencer7593 · Accepted Answer · 2015-01-31 07:11:57Z

If Nama is not unique in tablea or tableb, or if rows exist in tablea that don't have a matching row in tableb, for example:

tablea:

Nama    Jumlah
A       66
B       95
C       47
C       18

tableb:

Nama    Jumlah  Gaji
A       35      47
C       0       24   
D       27      30
Z       NULL    99

If an acceptable result is to return a single occurrence of each value of Nama along with the totals of Jumlah and Gaji, then one approach (assuming the datatypes of the Nama and Jumlah columns is compatible), and assuming that there isn't a requirement to return the rows in a particular sequence, one option is to combine the two sets with a UNION ALL operator into a derived table, and then use SUM() aggregate.

For example:

SELECT t.Nama
     , SUM(t.Jumlah) AS Jumlah
     , SUM(t.Gaji)   AS Gaji
  FROM ( SELECT b.Nama
              , b.Jumlah
              , b.Gaji
           FROM tableb b
          UNION ALL
         SELECT a.Nama
              , a.Jumlah
              , NULL
           FROM tablea a
       ) t
 GROUP BY t.Nama

Because of the derived table (i.e. the way that MySQL processes derived tables), this will likely not be the most efficient approach for large sets.

Collectives™ on Stack Overflow

combine two columns in 1 column mysql

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related