Mysql – SQL aggregation query for SUM, but only allow positive sums (otherwise 0)

aggregate-functionsMySQLsql

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 specifically in Mysql 5.5, would also be ok).

Best Answer

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 SUMs will probably be optimized to a single SUM under the hood.

Related Solutions

Try/except in django, good practices

You need to add this check:

from django.shortcuts import render

def index_view(request):

    if request.user.is_authenticated():
        profile = request.user.get_profile()
        return render(request,'index.html',{'profile': profile})
    else:
        return redirect('login/')

Or, you can use the built-in decorator which ensures that your view is only called with a logged in user:

from django.contrib.auth.decorators import login_required
from django.shortcuts import render

@login_required
def index_view(request):

    profile = request.user.get_profile()
    return render(request,'index.html',{'profile': profile})

If you use the login_required decorator you need to make sure you have a LOGIN_URL setting that points to the view that handles the login form for your site. By default this is /accounts/login/

I changed the method name to lowercase as CamelCase is usually for classes in Python. Also, you don't need two return statements since you are rendering the same template. Instead, in your index.html template you can do:

{% if profile %}
    You have a profile!
{% else %}
    You don't
{% endif %}

Tips and tricks with authentication are listed at the authentication entry in the documentation.

Best Answer

Related Solutions

Try/except in django, good practices

Related Topic