Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    42

    Post Unanswered: Calculating balance in stock statement

    Hi folks,

    I am a recent dotnet and php programmer. I have been assigned to a project that involves inventory management. It is based on postgresql server as backend on windows platform.

    My problem involves the following tables

    a) Stock Issue (stock_issue) with columns Entryno, Date, Itemname, Qty

    b) Stock Receipt (stock_receipt) with columns Entryno, Date, Itemname, Qty

    Both tables have similar definations. A entry is made in stock_issue whenever items go out and vice-versa in stock receipt.


    I have to generate a report with gives the sum of all issues and receipts , grouped itemwise. There is a extra fourth column with difference between the issue and receipt qty. Entryno and date are not required in the report.


    I have already generated a report by using the following SQL statement.

    Code:
    SELECT issue_stock.itemname, SUM(DISTINCT issue_stock.qty) AS QtyOut, SUM(DISTINCT stock_receipt.qty) AS QtyIn
    FROM issue_stock INNER JOIN
    stock_receipt ON issue_stock.itemname = stock_receipt.itemname
    GROUP BY issue_stock.itemname
    the output is in the following format



    Itemname QtyOut QtyIn

    Item1 20 12
    Item2 13 3
    Item3 8 1


    It is working ok.

    My problem is only to add a fourth column that displays the difference between QtyOut and QtyIn
    The related output must be as follows

    Itemname QtyOut QtyIn Balance

    Item1 20 12 8
    Item2 13 3 10
    Item3 8 1 7

    Please suggest any solution, related to, how to add a Balance column


    Thanx

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    wouldn't it be...
    Code:
    SELECT issue_stock.itemname, SUM(DISTINCT issue_stock.qty) AS QtyOut, SUM(DISTINCT stock_receipt.qty) AS QtyIn, SUM(DISTINCT issue_stock.qty) - SUM(DISTINCT stock_receipt.qty) as Balance
    ...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Aug 2011
    Posts
    42

    Thumbs up

    Quote Originally Posted by loquin View Post
    wouldn't it be...
    Code:
    SELECT issue_stock.itemname, SUM(DISTINCT issue_stock.qty) AS QtyOut, SUM(DISTINCT stock_receipt.qty) AS QtyIn, SUM(DISTINCT issue_stock.qty) - SUM(DISTINCT stock_receipt.qty) as Balance
    ...
    Oh, thanx,

    will it calculate sum of both quantities again, because i tried to place QtyOut-Qtyin as balance (since they are already being calculated) and it gives an error.

    anyway this solution works , it is splendid. I was really in need of it urgently.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by arvindps View Post
    will it calculate sum of both quantities again
    No they will only be calculated once.

    because i tried to place QtyOut-Qtyin as balance (since they are already being calculated) and it gives an error.
    You need to use a derived table in order to be able to use the column alias for the aggregate functions:

    Code:
    SELECT itemname, 
           QtyOut, 
           QtyIn,
           QtyOut - Qtyin as balance
    FROM (
        SELECT issue_stock.itemname, 
               SUM(DISTINCT issue_stock.qty) AS QtyOut, 
               SUM(DISTINCT stock_receipt.qty) AS QtyIn
        FROM issue_stock INNER JOIN
        stock_receipt ON issue_stock.itemname = stock_receipt.itemname
        GROUP BY issue_stock.itemname
    ) t

  5. #5
    Join Date
    Aug 2011
    Posts
    42

    Thumbs up

    Quote Originally Posted by shammat View Post
    No they will only be calculated once.

    You need to use a derived table in order to be able to use the column alias for the aggregate functions:

    Code:
    SELECT itemname, 
           QtyOut, 
           QtyIn,
           QtyOut - Qtyin as balance
    FROM (
        SELECT issue_stock.itemname, 
               SUM(DISTINCT issue_stock.qty) AS QtyOut, 
               SUM(DISTINCT stock_receipt.qty) AS QtyIn
        FROM issue_stock INNER JOIN
        stock_receipt ON issue_stock.itemname = stock_receipt.itemname
        GROUP BY issue_stock.itemname
    ) t
    Thanx a zillion, that query made me look great in my department.
    Although the time consumed was only slightly lower, but i have been to modify this query to suit other similar calculative output.

    I just hope this is a faster method. I will give it another try on monday.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by arvindps View Post
    I just hope this is a faster method. I will give it another try on monday.
    Faster than loquin's solution?
    I doubt it. The optimizer will most probably generate the same execution plan for both queries.

Tags for this Thread

Posting Permissions

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