如何关联销售表与明细表，按日期分组统计销量及销售额？

Hey Aris, let's work through this query to get the combined results you need. From what you've shared, you want to group by the date from your SALE TABLE (t_pembelian_tunai), while also pulling in aggregated totals from the DETAIL SALE TABLE (t_rinci_beli_tunai) linked by the order ID. Here are two straightforward ways to achieve this:

Method 1: Use a Subquery for Detail Totals #

First, we can calculate the total per order from the detail table as a subquery, then join it to the main sales table and aggregate by date:

SELECT 
    bt.tanggal AS date_sold,
    COUNT(DISTINCT bt.kd_op_beli_tunai) AS quantity_id_sold,
    SUM(ds.total_sold) AS daily_total_sold
FROM t_pembelian_tunai bt
-- Join with the pre-aggregated detail totals
JOIN (
    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
) ds ON bt.kd_op_beli_tunai = ds.id_sold
GROUP BY bt.tanggal
ORDER BY bt.tanggal;

Key Notes: #

• COUNT(DISTINCT bt.kd_op_beli_tunai) ensures we don't count the same order multiple times (since one order might have multiple detail rows).
• The subquery ds pre-calculates the total for each individual order, making the main aggregation cleaner.

Method 2: Direct Join + Aggregation (Simpler) #

If you prefer a more concise query, you can join the two tables directly and aggregate in one step:

SELECT 
    bt.tanggal AS date_sold,
    COUNT(DISTINCT bt.kd_op_beli_tunai) AS quantity_id_sold,
    SUM(rbt.harga_satuan * rbt.jumlah) AS daily_total_sold
FROM t_pembelian_tunai bt
JOIN t_rinci_beli_tunai rbt ON bt.kd_op_beli_tunai = rbt.kd_op_beli_tunai
GROUP BY bt.tanggal
ORDER BY bt.tanggal;

Including Orders with No Details #

If you need to include orders from t_pembelian_tunai that have no corresponding entries in t_rinci_beli_tunai (with total sales showing as 0 instead of excluding them), switch to a LEFT JOIN and use COALESCE to handle NULL values:

SELECT 
    bt.tanggal AS date_sold,
    COUNT(DISTINCT bt.kd_op_beli_tunai) AS quantity_id_sold,
    COALESCE(SUM(rbt.harga_satuan * rbt.jumlah), 0) AS daily_total_sold
FROM t_pembelian_tunai bt
LEFT JOIN t_rinci_beli_tunai rbt ON bt.kd_op_beli_tunai = rbt.kd_op_beli_tunai
GROUP BY bt.tanggal
ORDER BY bt.tanggal;

Either of these methods will give you the combined result set you're looking for: daily order counts alongside daily total sales.

内容的提问来源于stack exchange，提问作者Aris