Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Unanswered: Please help on SUM Query

    I am calculating the sum of quantity from purchase table and sales table to get the current stock.

    here is the query which is giving error

    Select SUM(Purchase.QTY) - SUM(Sales.QTY) as Stock from Purchase, Sales Group By Purchase.QTY, Sales.QTY

    please help on this

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I believe you have misse dout on the join condition between the two tables. You must be having a product_id or something on which to join.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jan 2004
    Posts
    17
    yes I do have ItemID

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Please help on SUM Query

    simply

    Select SUM(Purchase.QTY) - SUM(Sales.QTY) as Stock
    from Purchase, Sales

  5. #5
    Join Date
    Jan 2004
    Posts
    17

    Re: Please help on SUM Query

    Originally posted by Karolyn
    simply

    Select SUM(Purchase.QTY) - SUM(Sales.QTY) as Stock
    from Purchase, Sales
    .....

    I tried it several times does not work....

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Please help on SUM Query

    what's the message error ?

  7. #7
    Join Date
    Jan 2004
    Posts
    17

    Re: Please help on SUM Query

    Originally posted by djariwala
    .....

    I tried it several times does not work....
    sorry,...it works, i apologies for my stupid mistake.

    anyways thank u very much.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Select itemid,SUM(Purchase.QTY-Sales.QTY) as Stock from Purchase, Sales where purchase.itemid = sales.itemid group by itemid


    Is this what you are trying to do
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    was simpler than that
    no joins were needed ... :-)

  10. #10
    Join Date
    Jan 2004
    Posts
    17

    Angry

    Originally posted by Karolyn
    was simpler than that
    no joins were needed ... :-)
    SELECT SUM(PBillTrans.QTY) - SUM(SBillTRans.QTY) as Stock from PBilltrans,sbilltrans

    There's still a problem

    If there is nothing in any of the coloumn it returns nothing..

    i.e if purchas.QTY is 0 and Sales.QTY is 10 it should return -10... but it is returning no value.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Why so ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jan 2004
    Posts
    17
    Originally posted by Enigma
    Why so ????
    There r 10 items in sales table & nothing in purcase.
    purchase table is empty.

    so, i guess its returning nothing - 10 = nothing,

    my QTY datatype is numeric. &

    I'm allowing negetive stock values

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    select coalesce(Sum(Y),0) - coalesce(Sum(X),0) From Table1,Table2

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    But would STOCK not be itemwise ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Jan 2004
    Posts
    17

    error

    Originally posted by Karolyn
    select coalesce(Sum(Y),0) - coalesce(Sum(X),0) From Table1,Table2
    SELECT coalesce(SUM(Pbilltrans.qty),0) - coalesce(sum(sbilltrans.qty),0) as stock from pbilltrans,sbilltrans

    coalesce undefined function.

    I forgot to mention i'm using access as backend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •