SQL help i need to find the inventory remaining in my office-CodePudding

In sql help i have 3 tables, table one is asset table which is as follow

id	asset_code	asset_name	asset_group	asset_quantity
1	A001	demo asset	4	5
2	A002	demo asset 2	6	3

and another table is asset_allocation

id	asset_id	allocated_quantity	allocated_location
1	1	2	IT office
2	1	1	main hall

the last table is asset_liquidated which will present assets that are no longer going to be used

id	asset_id	liquidated_quantity
1	1	2
2	1	1

lets say i have 5 computers and i have allocated 3 computers and 1 is no longer going to be used so i should be remaining with 1 computer so now how do i make sql auto generate this math for me

CodePudding user response：

You need to use aggregation and the join your tables -

SELECT id, asset_code, asset_name, asset_group, asset_quantity,
       asset_quantity - COALESCE(AA.allocated_quantity, 0) - COALESCE(AL.liquidated_quantity, 0) available_quantity
  FROM asset A
  LEFT JOIN (SELECT asset_id, SUM(allocated_quantity) allocated_quantity
               FROM asset_allocation
              GROUP BY asset_id) AA ON A.id = AA.asset_id
  LEFT JOIN (SELECT asset_id, SUM(liquidated_quantity) liquidated_quantity
               FROM asset_liquidated
              GROUP BY asset_id) AL ON A.id = AL.asset_id

This query will give you -1 as available_quantity for asset_id 1 as you have only 5 available, 3 of them are allotted and 3 are liquidated as per your sample data.

CodePudding user response：

Please see if this helps

SELECT
asset_quantity AS Total_Assets
,ISNULL(allocated_quantity, 0) allocated_quantity
,ISNULL(liquidated_quantity, 0) liquidated_quantity
FROM asset
LEFT OUTER JOIN (
 SELECT
asset_id, SUM(allocated_quantity) AS allocated_quantity
 FROM asset_allocation
GROUP BY asset_id
) asset_allocation2
ON asset_allocation2.asset_id = asset.id
LEFT OUTER JOIN (
 SELECT
asset_id, SUM(liquidated_quantity) AS liquidated_quantity
 FROM asset_liquidated 
GROUP BY asset_id
) asset_liquidated 2
ON asset_liquidated 2.asset_id = asset.id