CODEWITHSUNDEEP
mysqlsql
Aris
Aris

Reputation: 37

how to join 2 sql ( sum(table 1) and count(table 2 ) ) and group by date from table 1?

first table and second table relate by id_sold

query in table 1 as "SALE TABLE" 

select tanggal as date_sold, count(kd_op_beli_tunai) as quantity_id_sold
from t_pembelian_tunai
group by tanggal

result 1

enter image description here

query in table 2 as "DETAIL SALE TABLE"

select kd_op_beli_tunai as id_sold, sum(harga_satuan * jumlah) as total_sold
from t_rinci_beli_tunai
group by kd_op_beli_tunai

result 2

enter image description here

what i want is like this one

enter image description here

and this is what i have tried

 select bt.tanggal as date_sold, count(bt.kd_op_beli_tunai) as quantity_id_sold, sum(rbt.harga_satuan * rbt.jumlah) as total_sold
    from t_pembelian_tunai as bt, t_rinci_beli_tunai as rbt
    where bt.kd_op_beli_tunai = rbt.kd_op_beli_tunai
    GROUP by tanggal
    ORDER by tanggal DESC

the result for that is

enter image description here

Upvotes: 0

Views: 46

Answers (1)

D-Shih
D-Shih

Reputation: 46239

You can try this query.

SELECT  bt.tanggal as date_sold,bt.quantity_id_sold,rbt.total_sold
FROM
(
    select kd_op_beli_tunai,
           tanggal, 
           count(kd_op_beli_tunai) as quantity_id_sold
    from t_pembelian_tunai
    group by tanggal,kd_op_beli_tunai
)bt
INNER JOIN 
(
    select kd_op_beli_tunai, sum(harga_satuan * jumlah) as total_sold
    from t_rinci_beli_tunai
    group by kd_op_beli_tunai
)rbt on bt.kd_op_beli_tunai = rbt.kd_op_beli_tunai 
ORDER by bt.tanggal

Upvotes: 1

Related Questions