Need SQL query to eliminate rows of a single table that net to zero

[jpercival]

I have a single table with items and quantities as follows:

Items Quantity

xyz -12
xyz 12
xyz -12
xyz 12
xyz 12
abc -25
abc -25
abc 25
abc 25
abc 25
abc 25

I want to run a query that will Delete the first four xyz rows and the first four abc rows because they will net to a zero quantity. As you can see, using item as the key I could be left with (none), one or more rows by item. Any help would be greatly appreciated!

 

[mdthornton]

Why not just sum up all the quantities (grouping by item of course). This would give you:

xyz 12
abc 50

or do you need:

xyz 12
abc 25
abc 25 Best Regards,
Mike

 

[jpercival]

Yes Mike, unfortunately I need

xyz 12
abc 25
abc 25

Thanks anyway.

 

[mdthornton]

Will the numbers always be equal and opposite, ie: if there's a -12 there's at least one 12? Best Regards,
Mike

 

[jpercival]

Yes they will. I have tried some processing with the absolute value of quantity column but haven't been able to figure out how to specify which resulting rows I want deleted.

???

 

[mdthornton]

Ok, without adding some kind of id column to the table this is the best I can come up with:

SELECT Table9.a, Abs() AS Expr2, Sum(Sgn()) AS Expr1
FROM Table9
GROUP BY Table9.a, Abs();

on you data above this will give:

col1 col2 col3
xyz 12 1
abc 25 2

You could then process this in VB and recreate your table, ie, process results and do insert col1, col2 col3 times into a tmpTable. Then delete original rows and load in from tmp table.


Best Regards,
Mike