mysql - 如何连接2个sql( sum(table 1) 和 count(table 2 ) )并按表1中的日期分组？

Question

第一个表和第二个表通过 id_sold 关联

表 1 中的查询为“SALE TABLE”

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

结果1

表 2 中的查询为“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

结果2

我想要的是这样的

这就是我尝试过的

 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

结果是

Answer 1

您可以尝试此查询。

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