Home / SQL Tutorial / How to Use SQL SUM Function to Calculate Total

How to Use SQL SUM Function to Calculate Total

Summary: in this tutorial, we will show you how to use SQL SUM aggregate function to calculate the total of a set of values.

Introduction to SQL SUM function

The SUM function is an aggregate function that calculates the total of a set of numbers. The syntax of the SUM function is simple as follows:

SUM( DISTINCT or ALL numeric_expression)

Unlike other SQL aggregate functions, the SUM function only accept the expression that is evaluated to numeric values.

ALL and DISTINCT are modifiers.

  • DISTINCT means that the SUM function calculates the total of all unique values.
  • ALL means that the SUM function calculates the total of all values including duplicates. ALL is default modifier if you do not indicate any modifier before the expression.

Let’s have a couple of examples that apply the SUM functions.

Examples of using SQL SUM function

Simple example of using SQL SUM function

To calculate the total after discount of all orders:

  • First, we use the SUM function to calculate the total of all products sold for each order to get the total before discount.
  • Second, we calculate total discount by multiply the total before discount and discount of that order.
  • Finally, we subtract total discount from the total before discount.

We use the GROUP BY clause in the query so the SUM function calculates for each order identified by the orderid. The following is the query to perform the calculation:

SELECT orderid,
      discount,
(

SUM(unitprice * quantity) -
SUM(unitprice* quantity) * discount
) AS "grand total"
FROM order_details
GROUP BY orderid

SQL SUM function - Sale Order Grand Total

Advanced usage of the SQL SUM function example

With the SUM function and the GROUP BY clause, we can even answer more challenging business question such as displaying top 10 customers by sales as the following query:

SELECT customers.customerid,
companyname,
(SUM(unitprice * quantity) - SUM(unitprice * quantity) * discount) AS total
FROM order_details
INNER JOIN orders ON orders.orderid = order_details.orderid
INNER JOIN customers ON customers.customerid = orders.customerid
GROUP BY customers.customerid
ORDER BY total DESC
LIMIT 10

SQL SUM - Top 10 Customers by Revenue

The query works in MySQL database system because we use LIMIT clause that is used in MySQL. In Microsoft SQL Server, you can use the following query to find top 10 customers by sales.

SELECT TOP 10 customers.customerid,
companyname,
(SUM(unitprice * quantity) - SUM(unitprice * quantity) * discount) AS total
FROM order_details
INNER JOIN orders ON orders.orderid = order_details.orderid
INNER JOIN customers ON customers.customerid = orders.customerid
GROUP BY customers.customerid
ORDER BY total DESC

You can even find the top 10 best selling products by sales as follows:

SELECT P.productid,
P.productname,
(
SUM(O.unitprice * quantity) -
SUM(O.unitprice * quantity) * discount) AS total
FROM order_details O
INNER JOIN products AS P ON P.productid = O.productid
GROUP BY P.productid
ORDER BY total DESC
LIMIT 10

SQL SUM - Top 10 best selling products

In this tutorial, we have shown you how to use the SQL SUM function to calculate the total of a set of values and answer some of most interesting business questions.