Przemysław Banaszek Przemysław Banaszek - 6 months ago 10
SQL Question

Delete rows in table that are sum of other rows per group

Group rows by

T
, and in each group find the row that is the largest or smallest (if values are negative) sum of other rows from that group, and delete that row (one for each group), if group does not have enough elements to find sum or enough but none of the rows indicates sum of others nothing happens

CREATE TABLE Test (
T varchar(10),
V int
);
INSERT INTO Test
VALUES ('A', 4),
('B', -5),
('C', 5),
('A', 2),
('B', -1),
('C', 10),
('A', 2),
('B', -4),
('C', 5),
('D', 0);


expected result:

A 2
A 2
B -1
B -4
C 5
C 5
D 0

TJB TJB
Answer

Like the comments, the requirements seem strange. The below code assumes that the summing is already pre-populated and merely removes the largest/smallest as long as the highest value is not 0.

if object_id('tempdb..#test') is not null
drop table #test

CREATE TABLE #Test (
    T   varchar(10),
    V   int
);
INSERT INTO #Test
    VALUES ('A', 4), ('B', -5), ('C', 5), ('A', 2), ('B', -1), ('C', 10), ('A', 2), ('B', -4), ('C', 5), ('D', 0);


if object_id('tempdb..#test2') is not null
drop table #test2

SELECT
T,
V,
ABS(V) as absV
INTO #TEST2
FROM #TEST
SELECT * FROM #TEST2

if object_id('tempdb..#max') is not null
drop table #max

SELECT
T,
MAX(absV) AS MaxAbsV
INTO #Max
FROM #TEST2
GROUP BY T
HAVING MAX(AbsV) != 0



DELETE #TEST2
FROM #TEST2
INNER JOIN #MAX ON #TEST2.T = #MAX.T AND #TEST2.absV = #Max.MaxAbsV

SELECT * FROM #TEST2
ORDER BY T ASC