unable to solve this complicated query, please help :( detailed question

Question

Table: sell

invoiceNo      cash       card        sellDate

    1                 2            0          29/6/2011

    2                 0            10        29/6/2011

    3                 0            10        29/6/2011 

    3                 6            0          29/6/2011 

 

Table: void

invoiceNo        total           voidDate

    3                10             29/6/2011 

    3                 6               29/6/2011 

 

after that, i come out with this query and result:

SELECT sellDate, Sum(card) as cardSum, Sum(cash) as cashSum FROM (SELECT invoiceNo, cash, card, sellDate FROM sell) GROUP BY sellDate

sellDate           cardSum          cashSum

29/6/2011           20                   8

 

 

but i wanted this result:

sellDate           cardSum          cashSum   voidTotal

29/6/2011           20                   8              16

 

 

 

Please help, i tried for more than 3 hours, and still not able to solve this :(

Answer 1

Try the following select statement:

select S.SellDate, S.CardSum, S.CashSum, V.VoidTotal from (select SellDate, sum(Card) as CardSum, sum(Cash) as CashSum from Sell GROUP BY SellDate) S

LEFT JOIN (select voidDate, SUM(Total) as voidTotal from Void GROUP BY VoidDate) V on S.SellDate = V.VoidDate

 

I assume you don't store time portion with your dates.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Thanks a lot! it helped!!

Answer 3

another question, how to verify the the voidTotal as 0 if no record found?

Answer 4

Add ISNULL or COALESCE for VoidTotal to convert NULL to 0

select S.SellDate, S.CardSum, S.CashSum, COALESCE(V.VoidTotal,0) as VoidTotal from (select SellDate, sum(Card) as CardSum, sum(Cash) as CashSum from Sell GROUP BY SellDate) S

LEFT JOIN (select voidDate, SUM(Total) as voidTotal from Void GROUP BY VoidDate) V on S.SellDate = V.VoidDate
 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

if i added ISNULL, error msg :

Wrong number of arguments used with function in query expression 'ISNULL(V.VoidTotal,0)'.

 

if COALESCE:

Undefined function 'COALESCE' in expression.

 

am using MS access

Answer 6

I see. This forum is for MS SQL Server. In Access you can use

IIF(V.VoidTotal IS NULL, 0, V.VoidTotal) as VoidTotal

or alternatively you can use NZ function http://www.techonthenet.com/access/functions/advanced/nz.php

NZ(V.VoidTotal, 0) as VoidTotal

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Thank again =) you are so good ~