CODEWITHSUNDEEP
mysqlsqldatabase
Supratman
Supratman

Reputation: 13

combine 2 tables in 1 row using join and group by

i have two tables = debitur and resume. I want to combine 2 tables in 1 row like this

What is the query?

SELECT lao, SUM(outstanding) as Outstanding, COUNT(lao) as jumlah
FROM debitur
GROUP BY lao

and

SELECT SUM(tgt_pergeseran) as Target
FROM resume
GROUP BY lao

Upvotes: 1

Views: 51

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a join between the two queries based on lao, but for this you need lao is subquery select too 

select 
    t1.lao, t1.Outstanding, t1.jumlah, t2.Target
from
    (select
         lao, sum(outstanding) as Outstanding, count(lao) as jumlah
     from
         debitur 
     group by
         lao)
inner join
    (select
         lao, sum(tgt_pergeseran) as Target 
     from
         resume 
     group by 
         lao) t2 on t2.lao = t1.lao

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

MySQL does not support full join. So, if you want all the records in both tables -- even when some are missing -- then I recommend union all/group by:

SELECT lao, SUM(outstanding) as Outstanding,
       COUNT(outstanding) as jumlah,
       SUM(target) as target
FROM ((SELECT d.loa, d.outstanding, null as target
       FROM debitur d
      ) UNION ALL
      (SELECT r.loa, NULL, r.tgt_pergeseran as target
       FROM resume r
      ) 
     ) rd
GROUP BY lao ;

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try This- 

SELECT 
A.lao,
A.Outstanding,
A.jumla,
B.Target 
FROM
(
    SELECT lao, 
    SUM(outstanding) as Outstanding, 
    COUNT(lao) as jumlah 
    FROM debitur 
    GROUP BY lao
 )A
INNER JOIN
(
    SELECT 
    lao,
    SUM(tgt_pergeseran) as Target 
    FROM resume 
    GROUP BY lao
) B
ON A.lao = B.lao

Upvotes: 1

Related Questions