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

  • 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 :(






    Tuesday, June 28, 2011 6:10 PM

Answers

  • 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

    • Marked as answer by melvintcs Tuesday, June 28, 2011 6:52 PM
    Tuesday, June 28, 2011 6:40 PM
  • 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
    • Marked as answer by melvintcs Tuesday, June 28, 2011 7:20 PM
    Tuesday, June 28, 2011 7:13 PM

All replies

  • 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

    • Marked as answer by melvintcs Tuesday, June 28, 2011 6:52 PM
    Tuesday, June 28, 2011 6:40 PM
  • Thanks a lot! it helped!!
    Tuesday, June 28, 2011 6:52 PM
  • another question, how to verify the the voidTotal as 0 if no record found?
    Tuesday, June 28, 2011 7:01 PM
  • 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
    Tuesday, June 28, 2011 7:03 PM
  • 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

    Tuesday, June 28, 2011 7:09 PM
  • 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
    • Marked as answer by melvintcs Tuesday, June 28, 2011 7:20 PM
    Tuesday, June 28, 2011 7:13 PM
  • Thank again =) you are so good ~
    Tuesday, June 28, 2011 7:20 PM