SQL Puzzle | Find Max except negative values

In this puzzle the requirement is to get the max value for each Id. Only exception is that if the maximum value in that group is 0 and negative values are present in that group then get sum of the negative values.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE WhatMaxy
(
	 Id INT
	,Vals INT
)
GO

INSERT INTO WhatMaxy VALUES
(1,-1),
(1,0),
(2,1),
(2,0),
(2,4),
(3,-1)
GO
INSERT INTO WhatMaxy VALUES(4,1),(4,-1)
INSERT INTO WhatMaxy VALUES(5,-1),(5,-12)
INSERT INTO WhatMaxy VALUES(6,-1),(6,-2),(6,0)
		 
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1

--
 
SELECT  k.Id, CASE WHEN k1.Vals < 0 AND k.Vals = 0 THEN k1.Vals ELSE k.Vals End Vals FROM 
(
	SELECT MAX(Vals) Vals, Id FROM WhatMaxy
	GROUP BY Id
)k
INNER JOIN 
(
	SELECT SUM(Vals) Vals, Id FROM WhatMaxy
	GROUP BY Id
)k1
ON k.Id = k1.Id
GO

--