SQL SELECT SUM() Function

SQL SUM() function returns the total sum of values of a numeric column in a table.

SUM() function Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

SQL SUM() function example – On a Specific column

In this example, we will see how to find out the sum of values of a numeric column in a table.

Table: Orders

ORDER_ID   CUSTOMER_NAME   ORDER_VALUE
--------   -------------   -----------
901         Ritesh          5000
911         Carl            9000
917         Kevin           85000
961         Paul            9000
998         Steve           6000

The following SQL statement finds the sum of the values of ORDER_VALUE column –

SELECT SUM(ORDER_VALUE)
FROM Orders;

Result:

SUM(ORDER_VALUE)
----------------
114000

SUM() function On multiple columns

We can use SUM() function on multiple columns of a table.

Table: Customers

CUST_ID    CUSTOMER_NAME   ORDER_VALUE    PREV_BALANCE
--------   -------------   -----------    ------------
703         Ritesh          5000            2000
715         Carl            9000            3000
714         Kevin           85000           1000
760         Paul            9000            0
797         Steve           6000            5000

The following SQL statement finds the sum of the values of ORDER_VALUE & PREV_BALANCE columns –

SELECT SUM(ORDER_VALUE + PREV_BALANCE)
FROM Customers;

Result:

SUM(ORDER_VALUE + PREV_BALANCE)
-------------------------------
125000

SQL SUM() with where clause

We can selectively find the sum of only those rows, which satisfy the given condition. To do this, we can use the where clause in the SQL statement. Lets take an example to understand this.

Table: Customers

CUST_ID    CUSTOMER_NAME   ORDER_VALUE    PREV_BALANCE
--------   -------------   -----------    ------------
703         Ritesh          5000            2000
715         Carl            9000            3000
714         Kevin           85000           1000
760         Paul            9000            0
797         Steve           6000            5000

The following SQL statement finds the sum of the values of ORDER_VALUE column where the ORDER_VALUE is greater than 8000 –

SELECT SUM(ORDER_VALUE)
FROM Customers
WHERE ORDER_VALUE > 8000;

Result:

SUM(ORDER_VALUE)
----------------
103000

SQL SUM() EXAMPLE with DISTINCT

In this example, we will see how to use DISTINCT with the SQL SUM() function.

Table: Orders

ORDER_ID   CUSTOMER_NAME   ORDER_VALUE
--------   -------------   -----------
901         Ritesh          5000
911         Carl            9000
917         Kevin           85000
961         Paul            9000
998         Steve           6000

The following SQL statement finds the sum of distinct order values from the table Orders. Here we have used the SQL alias to temporary rename the column name of the output.

SELECT SUM (DISTINCT ORDER_VALUE) AS "DISTINCT ORDER SUM"  
FROM Orders;

Result:

DISTINCT ORDER SUM
------------------
105000

Explanation: Since the order value 9000 exists twice in the table, it would be considered only once when finding the sum of distinct values.

SQL SUM function with GROUP BY clause

We can use the SUM() function with the GROUP BY clause as well.

Table: Customer

CUST_ID    CUSTOMER_NAME   ORDER_VALUE 
--------   -------------   ----------- 
703         Ritesh          5000
703         Ritesh          4000
703         Ritesh          1000
760         Paul            8000
760         Paul            6000

The following SQL statement finds the sum of order values of each individual customer and groups the result using customer name.

SELECT CUSTOMER_NAME, SUM(ORDER_VALUE) AS "Total Order"  
FROM Customer  
GROUP BY CUSTOMER_NAME;

Result:

CUSTOMER_NAME   Total Order
-------------   ----------------
Ritesh           10000
Paul             14000