CODEWITHSUNDEEP
sqlsql-server
Kevin Minehart
Kevin Minehart

Reputation: 492

SQL: Grouped table; delete reciprocal pairs?

Say I have a table like this called inventory, for example:

+----+------------+-------+
| id |Product_ID  | Count |
+----+------------+-------+
| 1  | 1          |     4 |
| 2  | 2          |     5 |
| 3  | 4          |     3 |
| 4  | 5          |     3 |
| 5  | 5          |    -3 |
| 6  | 2          |    -5 |
+----+------------+-------+

And the result of my query GroupedInventory AS (SELECT id, Product_ID, Count FROM inventory ORDER BY Product_ID gives me (stored in GroupedInventory): 

+----+------------+-------+
| id |Product_ID  | Count |
+----+------------+-------+
| 1  | 1          |     4 |
| 2  | 2          |     5 |
| 6  | 2          |    -5 |
| 3  | 4          |     3 |
| 4  | 5          |     3 |
| 5  | 5          |    -3 |
+----+------------+-------+

And I want to delete the matching positive and negative results. So in the end the table should turn into:

+----+------------+-------+
| id |Product_ID  | Count |
+----+------------+-------+
| 1  | 1          |     4 |
| 3  | 4          |     3 |
+----+------------+-------+

I deal with sequential languages and I just can't wrap my head around this. My mind is telling me to loop through the table, store the pairs and then delete the pairs by id in the original table.

Upvotes: 0

Views: 252

Answers (5)

user3111227
user3111227

Reputation: 69

with GroupedInventory as
(
 SELECT  Product_id, abs(sum(case when count < 0 then count else 0 end)) Negative, abs(sum(case when count > 0 then count else 0 end)) Positive 
 FROM inventory
 group by Product_id
)
delete from inventory
where Product_id in (select Product_id from GroupedInventory where positive = negative );

Although the validity also depends on unspecified rules, such as how many entries can exist for a given product_id.

Upvotes: 1

Payer Ahammed
Payer Ahammed

Reputation: 907

Try this one

DELETE inventory
FROM inventory
    INNER JOIN (
        SELECT Product_ID FROM inventory group by Product_ID HAVING SUM(Count)=0
    ) as t ON inventory.Product_ID = t.Product_ID

Upvotes: 0

Mahesh
Mahesh

Reputation: 8892

You can try using the OVER clause with PARTITION BY AS well, You can read it here. Here is the query with your given test data,

create table #temp
(id bigint,
 proid bigint,
 cnt bigint)


 insert into #temp
 values(1,1,4),
 (2,2,5),
 (3,4,3),
 (4,5,3),
 (5,5,-3),
 (6,2,-5)

 ;WITH cte 
 AS(
    SELECT id, proid, SUM(cnt) OVER(PARTITION BY proid) AS total 
    FROM #temp 
 )

 SELECT * 
 FROM cte 
 WHERE total <> 0


 Drop table #temp

Upvotes: 0

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

Use this uqery:

SELECT 
  MIN(GI.ID) AS ID, GI.Product_ID, SUM(GI.Count) As COUNT
FROM 
  GroupedInventory AS GI
GROUP BY 
  GI.Product_ID
HAVING 
  SUM(GI.Count) <> 0;

I use MIN and SUM so as result you will have calculation of sum grouped to MIN ID. I don't know if this is desired result. It is not clear.

Upvotes: 0

alybaba726
alybaba726

Reputation: 400

Try this:

SELECT id, Product_ID, SUM(Count)
FROM [GroupedInventory]
WHERE SUM(Count) <> 0
GROUP BY Product_ID, id

I'm not 100% positive that will work without testing it myself, let me know if you get a weird result.

Upvotes: 0

Related Questions