conditional sum in sql, get correct sum from another table based on a condition in a field

Question

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!

@saghar: why are you sotring a date in a text column. Don't do that. Use a real `date` datatype. Which DBMS are you using anyway? Postgres? — , May 10 '13 at 07:31

John Woo · Accepted Answer · 2013-05-10T07:29:07.913

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

conditional sum in sql, get correct sum from another table based on a condition in a field

1 Answers1