COALESCE结合SUM与Join表计算结果异常求助

Let's break down why your second query is returning wrong results, then fix it step by step.

Key Issues in Your Problematic Query #

Your second SQL has three critical flaws that throw off the calculations:

1. Incorrect UNION ALL Handling: You're aggregating each individual sales table first with SUM and MIN, which returns only one row per table (total units across all SKUs) instead of preserving SKU-level data. This completely breaks the SKU-level matching you need.
2. Accidental Cross Join: Using a comma between t1 and tbl_list_sku creates a Cartesian product (every row in t1 paired with every row in tbl_list_sku), which inflates your sums dramatically.
3. Joining a Single Sales Table: You're joining only tbl_sku_units_order instead of the combined sales data from all 5 tables, so you're not accounting for sales from DE/ES/FR/IT regions.

Correct Approach #

The right strategy is to reuse your working base query (the one that correctly sums units across all 5 tables per SKU), then join that aggregated result to tbl_list_sku to get the full list of SKUs with their sales data.

Fixed SQL Code #

SELECT
  ls.sku AS list_sku,
  COALESCE(agg.sku, 'Not Sold') AS sold_sku,
  COALESCE(agg.units_ordered, 0) AS units_ordered
FROM tbl_list_sku ls
LEFT JOIN (
  -- Reuse your original correct aggregation of all sales tables
  SELECT
    sku,
    SUM(units_ordered) AS units_ordered
  FROM (
    SELECT sku, units_ordered FROM tbl_sku_units_order
    UNION ALL
    SELECT sku, units_ordered FROM tbl_sku_units_order_de
    UNION ALL
    SELECT sku, units_ordered FROM tbl_sku_units_order_es
    UNION ALL
    SELECT sku, units_ordered FROM tbl_sku_units_order_fr
    UNION ALL
    SELECT sku, units_ordered FROM tbl_sku_units_order_it
  ) AllMarketplace
  GROUP BY sku
) agg ON agg.sku = ls.sku
ORDER BY units_ordered DESC;

How This Works #

1. Inner Aggregation Subquery: The agg subquery uses your original working logic to calculate total units sold per SKU across all 5 marketplaces. This ensures we have accurate, SKU-level sales totals.
2. LEFT JOIN to SKU List: By joining tbl_list_sku to this aggregated subquery, we keep every SKU from the list, even if it has no sales.
3. COALESCE for Fallback Values: COALESCE(agg.sku, 'Not Sold') shows "Not Sold" for SKUs with no sales data, and COALESCE(agg.units_ordered, 0) ensures those SKUs show 0 instead of NULL for units sold.

This will correctly return the total units for SKU H-Root-M012S-Black as 87, along with all SKUs from your list table and their respective sales (or 0 if none exist).

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