SQL query aggregation for SUM, but only allow positive amounts (otherwise 0)

Question

I want to query the order table and show the customer ID and the total amount of all his orders, however, orders can have positive or negative totals.

select customer_id, SUM(order_total) from orders group by customer_id; 

Now my question is: how can I do the following in a single sql query:

If the total amount is positive, I want to display it as is; if the total amount is negative, I just want to display 0 instead of the actual amount.

What I'm looking for is a function that can handle this, like the IFNULL function ( IFNULL(SUM(order_total),0) ), but instead of checking for null, it should check for a negative result.

Pseudocode:

 IFNEGATIVE(SUM(order_total),0) 

Is there an easy way in standard sql (or, in particular, in Mysql 5.5, it will be good too).

Answer 1

 SELECT customer_id, CASE WHEN SUM(order_total) < 0 THEN 0 ELSE SUM(order_total) END FROM orders GROUP BY customer_id; 

Check the execution plan, but 2 SUM are likely to be optimized for one SUM under the hood.

Answer 2

Try:

 select customer_id, GREATEST( SUM(order_total),0) from orders group by customer_id; 

Answer 3

Not tested, but something like this should do this:

 SELECT customer_id , IF( SUM(order_total) > 0, SUM(order_total), 0) AS sum FROM orders GROUP BY customer_id 

Answer 4

if I understand that it can only be wrapped GREATEST

 SELECT customer_id, GREATEST(0,SUM(order_total)) FROM orders GROUP BY customer_id; 

look at the link

Answer 5

Could you use the CASE statement?

Sort of:

 CASE WHEN [Field] < 0 THEN 0 

Or am I missing something?

Answer 6

 select Id,case when (sum(amount)<0) then 0 else sum(amount) end from tblsum group by Id 

Answer 7

You can also try:

 select (sum(fld) + abs(sum(fld))) / 2 from tbl 

Answer 8

To display only positive values ​​Use HAVING this way:

 select customer_id, SUM(order_total) from orders group by customer_id HAVING SUM(order_total) > 0; 

Otherwise use case as indicated elsewhere here