Ramadhani Ramadhani - 1 month ago 7
MySQL Question

SUM Group BY with JOIN SQL Wrong Output

I have 2 tables; the output is not as expected, when I try

group
with
join
.

Here are the tables:

table1

table1

table2

table2

Here is the code:

SELECT *, SUM(total_jual) AS totaljual, SUM(total_beli) AS totalbeli
FROM penjualan
JOIN pembelian ON penjualan.tgl_penjualan = pembelian.tgl_pembelian
GROUP BY penjualan.tgl_penjualan


I want the this output:
**2017-08-16** **4404000** **18051000**


How to achieve this ?

Answer Source

When joining you get one line for each permutation i.e. one line for each combination of where the join condition matches. This leads to four lines in your example where each value is duplicated. Try the select without the GROUP BY and you will see. In order to get the correct sum you need to join by an aggregated result from the second table:

SELECT penjualan.tgl_penjualan, 
       SUM(total_jual) AS totaljual, 
       max(b.total_beli) AS totalbeli 
FROM penjualan 
JOIN (SELECT tgl_pembelian, sum(total_beli) AS total_beli FROM pembelian
      GROUP BY tgl_pembelian) AS b 
  ON penjualan.tgl_penjualan = b.tgl_pembelian 
GROUP BY penjualan.tgl_penjualan;

Appologies for any syntax error. I have not tested the sql.