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

Id Vals
1 -1
1 0
2 1
2 0
2 4
3 -1
4 1
4 -1
5 -1
5 -12
6 -1
6 -2
6 0

Expected Output

Id Vals
1 -1
2 4
3 -1
4 1
5 -1
6 -3

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”.

 ``` -- 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 -- ```