SQL aggregation query for SUM, but only allow positive sums (otherwise 0)
I want to query an orders table and show the customer id and the total of all his orders, however the orders can have positive or negative totals.
select customer_id, SUM(order_total) from orders group by customer_id;
Now my question - how can I achieve the following in one sql query:
If the total sum is positive, I want to display it as is; if the total sum is negative, I just want to display 0 instead the actual amount.
What I am looking for is a function that can handle this, similar to the IFNULL
function (IFNULL(SUM(order_total),0)
), but instead of checking for null, it should check for a negative result.
Pseudo code:
IFNEGATIVE(SUM(order_total),0)
Is there a simple way in standard sql (or speci开发者_如何学Gofically in Mysql 5.5, would also be ok).
SELECT customer_id,
CASE
WHEN SUM(order_total) < 0 THEN 0
ELSE SUM(order_total)
END
FROM orders
GROUP BY customer_id;
Check your execution plan, but the 2 SUM
s will probably be optimized to a single SUM
under the hood.
Try with:
select customer_id, GREATEST( SUM(order_total),0) from orders group by customer_id;
Not tested, but something like this should do it:
SELECT customer_id , IF( SUM(order_total) > 0, SUM(order_total), 0) AS sum FROM orders GROUP BY customer_id
Could you not use a CASE statement?
Something like:
CASE WHEN [Field] < 0 THEN 0
Or did I miss something?
if i understand its only wrap it with GREATEST
SELECT customer_id, GREATEST(0,SUM(order_total))
FROM orders GROUP BY customer_id;
look on the link
select Id,case when (sum(amount)<0) then 0 else sum(amount) end from tblsum group by Id
You can also try;
select
(sum(fld) + abs(sum(fld))) / 2
from tbl
To only display positive Use HAVING thus:
select customer_id, SUM(order_total) from orders group by customer_id HAVING SUM(order_total) > 0;
Otherwise use case as listed elsewhere here
精彩评论