CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 155

mysql Table Sum of Positive and Negative Numbers

I have a column which has positive & negative numbers. Is it possible to have sum of all the positive numbers in Col1, negative numbers in Col2 and (Col1 - Col2) in Col3. Then sort by the last Col.

    Current table         New Table
    ID Score              ID   Pos  Neg   Diff            
    1   3                  3    5     0     5
    1   1                  1    4     1     3
    1  -1                  2    2     1     1
    2   1
    2  -1
    2   1
    3   3
    3   1
    3   1

This gives me the total but i would like to list the Pos and Neg numbers as well.

SELECT ID, SUM(Score) as total FROM results 
GROUP BY ID ORDER BY total DESC

Upvotes: 0

Views: 1511

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Just use a standard pivot query with separate conditional aggregations for the positive and negative numbers.

SELECT
    ID,
    SUM(CASE WHEN Score >= 0 THEN Score    ELSE 0 END) AS Pos,
    SUM(CASE WHEN Score <  0 THEN -1*Score ELSE 0 END) AS Neg,
    SUM(Score) AS Diff
FROM results
GROUP BY ID
ORDER BY ID

Demo

Upvotes: 3

Related Questions