NETT QTY required from single column 1

[wickyd]

How do I go from ...


DOCNO ITEM QTY
INV11 A 5
INV11 E 1
INV11 G 2
INV12 B 6
INV13 B 2
CRN03 A 5
INV14 C 4
INV15 A 2
CRN04 A 1


to ...


ITEM QTY
A 1
B 8
C 4
E 1
G 2


In other words, the SUM of the CRN QTY must be deleted from the SUM of the INV QTY column to get the NETTQTY for each particular item.

I am using DBISAM built into our accounting software.

Thank you.

Kind regards
wickyd

 

[swampBoogie]

select item,
  sum(case substring(docno from 1 for 3)
    when 'INV' then qty else - qty end )
 from t
group by item

 

[wickyd]

I should have made my sample data clearer

INV and CRN are the only ones I am concerned with, but there are others which would affect the data, like GRV etc, so the ELSE -QTY would cause a problem.

Also, DBISAM doesn't support CASE.

 

[swampBoogie]

Well, what type of conditional operators do it support?

iif or if or ...

or does it support scalar subqueries?

select distinct item,
(select sum(qty) from t 
  where item = ct.item
    and docno like 'INV%')
- 
(select sum(qty) from t
  where item = ct.item
    and docno like 'CRN%')
 from t as ct

 

[wickyd]

Thank you for your help, I will try these suggestions and get back to you later today.

 

[wickyd]

Here it goes ...

SELECT ITEM, SUM(IF(SUBSTRING(DOCNO FROM 1 FOR 3) = "INV",QTY,IF(SUBSTRING(DOCNO FROM 1 FOR 3) = "CRN",-QTY,0)))NETTQTY FROM INVITEMS
GROUP BY ITEM
ORDER BY ITEM ASCENDING

Thank you swampBoogie