Calculating balance in stock statement

arvindps · #1 (**permalink**) 08-25-11, 09:57

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

loquin · #2 (**permalink**) 08-25-11, 13:24

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
...

arvindps · #3 (**permalink**) 08-26-11, 04:18

Quote:

Originally Posted by loquin

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.

shammat · #4 (**permalink**) 08-26-11, 10:20

Quote:

Originally Posted by arvindps

will it calculate sum of both quantities again

No they will only be calculated once.

Quote:

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

arvindps · #5 (**permalink**) 08-27-11, 10:36

Quote:

Originally Posted by shammat

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.