Ad

SQL Help I Need To Find The Inventory Remaining In My Office (i Want To Know Unallocated Asset Remaining ))

- 1 answer

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

idasset_codeasset_nameasset_groupasset_quantity
1A001demo asset45
2A002demo asset 263

and another table is asset_allocation

idasset_idallocated_quantityallocated_location
112IT office
211main hall

now the scenario is that i am creating an office asset management system so after allocating any asset i want to know the remaining assets are not allocated (in short i want to know the unallocated asset remaining in the office)

lets say i have 5 computers and i have allocated 3 computers so i should be remaining with 2 computers so now how do i make sql auto generate this math for me

Ad

Answer

You must get the sum of allocations per asset. You can do this in a correlated subquery in the select clause or in a non-correlated subquery in the from clause. If you choose the latter option, outer join this result to the assets in order to keep assets that have no allocations. Use COALESCE to turn the null allocations into zero allocations so you can calculate the difference with them:

select a.*, a.asset_quantity - coalesce(aa.sum_qty, 0) as remains
from asset a
left join
(
  select asset_id, sum(allocated_quantity) as sum_qty
  from asset_allocation
  group by asset_id
) aa on aa.asset_id = a.id
order by a.id;
Ad
source: stackoverflow.com
Ad