問題描述
sql中的條件總和，根據字段中的條件從另一個表中獲取正確的總和 (conditional sum in sql, get correct sum from another table based on a condition in a field)
I have a table (tbl1) like this, in which the sale amount for "store 3" is incorrect.
**date store sale**
Mar, 2013 store 1 100
Apr, 2013 store 1 80
Mar, 2013 store 2 70
Mar, 2013 store 3 125
Apr, 2013 store 3 80
The correct amount is in another table (tbl2):
**date store sale**
Mar, 2013 store 3 140
Apr, 2013 store 3 170
Now, I need to write a query that generates results as below:
**store total_sale**
store 1 180
store 2 70
store 3 310
I tried different ways of writing CASE statements, but I'm getting wrong total. I have simplified the real question here hoping get help from the community. Thank you!
參考解法
方法 1:
give this a try,
SELECT a.Store,
COALESCE(c.TotalSale, b.TotalSale) AS TotalSale
FROM (SELECT DISTINCT Store FROM Table1) a
INNER JOIN
(
SELECT Store, SUM(sale) TotalSale
FROM Table1
GROUP BY Store
) b ON a.Store = b.Store
LEFT JOIN
(
SELECT Store, SUM(sale) TotalSale
FROM Table2
GROUP BY Store
) c ON a.Store = c.Store
- SQLFiddle Demo