column which have the same id

Imran Bashir

10y978

i have table name stock .in which 5 column name id productId stockIn stockOut And VoucherType and i want to check the if productId=1 and voucherType=1 that count all the column in stockIn witch the same ProductId and Sum All the value in stockIn then also count in stockout with same product id and sum if the stockin sum is greater then stockout that return true...

id ' productid ' stockin 'stockout 'VoucherType
-----------------------------------------
1 | 1 | 5 | 0 | 1
2 | 1 | 5 | 0 | 1
3 | 1 | 5 | 0 | 1
4 | 2 | 5 | 0 | 1
5 | 2 | 0 | 5 | 2
6 | 1 | 0 | 2 | 2
7 | 2 | 0 | 3 | 2

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Imran Bashir10y ago
this is working for me i want to also add the value which come at run time and these value add in sum(stockOut) and to make this store proc
===================================================
SELECT productId

, COUNT(stockIn) as Count_stockIn
, SUM(stockIn) as Sum_stockIn
, COUNT(stockOut) as Count_stockOut
, SUM(stockOut) as Sum_stockOut
, CASE WHEN SUM(stockIn) >= SUM(stockOut) THEN 'True' ELSE 'False' END AS IsInGreater
From Stock
Group By productId
Having productId = 1
Manas Mohapatra10y ago
Try with below query:
SELECT ProductID, IIF ( ISNULL(SUM(StockIn), 0) > ISNULL(SUM(StockOut), 0), 'TRUE', 'FALSE' ) AS Result
FROM Stock
GROUP BY ProductID
WHERE VoucherType = 1
Note: IIF is the function which works SQLServer 2012 onwards..
Praveen Sreeram10y ago
Hi Imran,
You should use Group By along with Aggregate functions for this. Please give a try for your requirement and let us know if you need any assistance while you are trying your self. That's the best way of learning things.
Thanks,
Prawin