CODEWITHSUNDEEP
mysqlgroup-bysumsign
JollyGood
JollyGood

Reputation: 55

mysql group by aggregation as column

I have a table with two relevant fields name and value. I want to make a query that will give me the sum of all the negative values and all the positive values for each name.

The best I got was the following code:

SELECT `name`,sign(`value`),sum(`value`)
FROM `testing` 
WHERE `value` != 0 
GROUP BY `name`,sign(`value`)

it gave me these results:

name | sign(`value`) | sum(`value`)
-----------------------------------
A    |      -1       |    -9
A    |       1       |    21
B    |      -1       |   -35
B    |       1       |     8
C    |      -1       |   -16
C    |       1       |    21

Which are the results I wanted but not the way I wanted them.

Can I make it look like this somehow?

name |  -1  |   1
-------------------
A    |  -9  |  21
B    | -35  |   8
C    | -16  |  21

This is my first question, so I hope I didn't write it in a completely humiliating manner.

Thanks in advance.

Upvotes: 2

Views: 38

Answers (1)

Asaph
Asaph

Reputation: 162831

A case statement comes in handy in situations like this.

select name,
sum(case when value > 0 then value else 0 end) as positive_sum,
sum(case when value < 0 then value else 0 end) as negative_sum
from testing
group by name;

Upvotes: 1

Related Questions