14,664,481 members
Rate this:
See more:
Hi,
I have the below table values from the SQL group by Query.
I need to find out the Available Quantity.

For Example,
Total Qty = 10 for the 6715 (Groupby) Identification
Total Sum Qty = 2 + 2 +2 = 6 for the 6715 (Groupby) Identification.
I want the result  Total Qty – TotalSumQty  10 – 6 = 4 (Available Qty).

See my Query:
```select
unit,
Number,
L.identification as identification,
RequiredQty,
sum(RequiredQty) as TotalSumQty,
I.TotalQty,
(I.TotalQty – I.RequiredQty) as AvailableQty,
convert(varchar,AvailabilityDate,101) as AvailabilityDate
from
tbl1 L
INNER JOIN
Tbl2 I
on
L. identification = I. identification
WHERE gi.ident='6715'
group by Number, unit,L. identification,requiredQty,I.TotalQty,AvailabilityDate```

```unit	Number	Identification	RequiredQty	TotalSumQty	TotalQty	AvailableQty
1-GD-B-08225	AB-00009-S10B-1	6715	1	2	10	?
2-GD-B-08275	AB-00009-S10B-1	6715	1	2		?
3-GD-B-08324	AB-00009-S10B-1	6715	1	2		?```
Posted
Updated 6-Sep-11 23:09pm
v2

Rate this:

## Solution 1

Try using

`sum(I.TotalQty – I.RequiredQty) as AvailableQty,`

`(I.TotalQty – I.RequiredQty) as AvailableQty,`

if this does not solve your issue then please post the table structure of tbl1 & tbl2.
v2