mysql - 如何加入2 sql(sum(表1)和count(表2))并按表1中的日期分组？

第一个表和第二个表由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中查询为“详细销售表”

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

结果是

最佳答案

您可以尝试此查询。

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