SQL: sum rows with conditions-CodePudding

I have a table in the database as follows:

date       account      side     size     
2022-01-01 1            buy      50
2022-01-01 1            sell     25
2022-01-01 1            buy      35
2022-01-01 1            sell     10
2022-01-01 2            buy      100
2022-01-01 2            sell     50
2022-01-02 1            buy      10
2022-01-02 1            sell     10
2022-01-02 2            buy      100
2022-01-02 2            sell     10

What I want is to subtract the size when the side is 'sell' and add it when it is buy to have something like this:

date       account      volume
2022-01-01 1            50
2022-01-01 2            50
2022-01-02 1            0  
2022-01-02 2            90

I have tried the following

select date, account, sum(case when size='sell' then size = -size else size = size end) as volume
group by date, account, side, size

But I get an error: ERROR: function sum(boolean) does not exist. HINT: No function matches the given name and argument types. You might need to add explicit type casts.

What am I doing wrong?

CodePudding user response：

Conditional aggregation is the way to go here, but your logic is a bit off. Also, you want to aggregate by only the date and account.

SELECT date, account,
       SUM(CASE WHEN size = 'buy' THEN size ELSE -size END) AS volume
FROM yourTable
GROUP BY date, account
ORDER BY date, account;

CodePudding user response：

Tim's answer is correct, and he addresses the multiple issues you have. I just want to expand on WHY you are getting the error you are getting with the sum/case combo so you can understand what you're doing wrong there.

SUM just tries to add up all the values, as its name suggests, and addition is really only valid with numeric values. You are actually lucky that you got the error message about the invalid Boolean values, because some languages would have just cast them to boolean integers (1 or 0) and summed that.

The issue is with your use of CASE.

sum(
  case 
    when size ='sell' then size = -size 
    else size = size 
  end
)

You are trying to use it to alter a value (you are trying to set the value of size to -size if the condition is met), but the CASE clause is actually going to return a value, whatever is after the then and else operators: in this case it will return either size = -size or size = size. In both cases you aren't return size or -size: you're returning the result of the comparison, either true or false, and then you are passing these Boolean values to SUM.

So what you want to do is make sure that the part after then or else matches your (and SUM's) expectations: a summable numeric value:

sum(
  case 
    when size ='sell' then -size 
    else size 
  end
)