mysql总数和多行的差异

Question

I'm new to mysql. I'm trying to write a query to calculate the total and difference for multiple rows, and then put them in specific columns.

Eg. I want to sum all Entity'A' of Type'1' then subtract from the result, the sum of Entity'A' of type'0'. While simultaneously populating a table to show where this difference is positive or negative.

Sample table:

Entity Type   Value
A      1      200
B      1      500
C      0      350
B      0      150
C      1      100
A      1      50
A      1      350

Expected Output:

Entity    Diff-Positive    Diff-Negative
A         600
B         350
C                          250

Note that Entity'A' for example may not have an entry for Type'0'

Answer 1

This is basically aggregation. You need a conditional to split the values into separate columns. One way uses greatest():

select entity,
       greatest(sum(case when type = 1 then value else - value end), 0) as diff_positive,
       greatest(sum(case when type = 0 then value else - value end), 0) as diff_positive    
from t
group by entity;