CODEWITHSUNDEEP
mysql
Muhammad Jody
Muhammad Jody

Reputation: 7

combine two columns in 1 column mysql

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

Upvotes: 0

Views: 63

Answers (2)

spencer7593
spencer7593

Reputation: 108510

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.

Upvotes: 0

SMA
SMA

Reputation: 37103

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

Upvotes: 2

Related Questions