MySQL SUM
Summary: in this tutorial, you will learn how to use the MySQL SUM()
function to calculate the sum of values in a set.
Introduction to the MySQL SUM()
function
The SUM()
the function is an aggregate function that allows you to calculate the sum of values in a set. The syntax of the SUM()
the function is as follows:
SUM(DISTINCT expression)
Here is how the SUM()
function works:
- If you use the
SUM()
function in aSELECT
a statement that returns no row, theSUM()
function returnsNULL
, not zero. - The
DISTINCT
option instructs theSUM()
function to calculate the sum of only distinct values in a set. - The
SUM()
function ignores theNULL
values in the calculation.
MySQL SUM()
function illustration
First, create a new table named sum_demo
:
CREATE TABLE sum_demo (
n INT
);
Then, insert some rows into the sum_demo
table:
INSERT INTO sum_demo(n)
VALUES(1),(1),(2),(NULL),(3);
Third, use the SUM()
function to calculate the total values in the n
column:
SELECT
SUM(n)
FROM
sum_demo;
As you can see, the SUM()
the function calculates the total of 1, 1, 2, and 3. And it ignores NULL.
Finally, use the SUM()
with the DISTINCT
option to calculate the total values in the n
column:
SELECT
SUM(DISTINCT n)
FROM
sum_demo;
In this case, the SUM()
with the DISTINCT
the option only calculates the sum of distinct values which are 1, 2, and 3.
MySQL SUM()
function examples
Let’s take a look at the table orderdetails
in the sample database.
1) Simple MySQL SUM()
function example
This example uses the SUM()
function to get the total number of items of the order details:
SELECT
SUM(quantityOrdered) SalesQuantity
FROM
orderdetails;
2) MySQL SUM()
function with expression example
The following shows the order line items of order number 10110:
SELECT
orderNumber,
quantityOrdered,
priceEach
FROM
orderdetails
WHERE
orderNumber = 10100;
To calculate the total for order number 10110, you use the SUM()
function as follows:
SELECT
SUM(quantityOrdered * priceEach) orderTotal
FROM
orderdetails
WHERE
orderNumber = 10100;
In this tutorial, the SUM()
the function calculates the total of the following expression of all order line items of the order number 10110:
quantityOrdered * priceEach
3) MySQL SUM()
with the GROUP BY
clause example
The SUM()
the function is often used with the GROUP BY
clause to calculate the sum for each group.
For example, you can calculate the total amount of each order by using the SUM()
function with the GROUP BY
clause as shown in the following query:
SELECT
orderNumber,
SUM(quantityOrdered * priceEach) orderTotal
FROM
orderdetails
GROUP BY
orderNumber
ORDER BY
orderTotal DESC;
In this example:
- The
GROUP BY
clause divides order details into groups grouped by the order number. - The
SUM()
the function calculates the total of each amount of each order.
4) MySQL SUM()
with HAVING
clause example
You can use the SUM()
function in the HAVING
clause to filter the group. This example illustrates how to select orders whose order amounts are greater than 60,000
.
SELECT
orderNumber,
SUM(quantityOrdered * priceEach) orderTotal
FROM
orderdetails
GROUP BY
orderNumber
HAVING
SUM(quantityOrdered * priceEach) > 60000
ORDER BY
orderTotal;
5) MySQL SUM()
with NULL
example
The SUM()
function returns NULL
if the result set is empty. Sometimes, you may want the SUM()
function to return zero instead of NULL
.
In this case, you can use the COALESCE()
function. The COALESCE
the function accepts two arguments and returns the second argument if the first argument is NULL
; otherwise, it returns the first argument.
See the following query:
SELECT
COALESCE(SUM(quantityOrdered * priceEach), 0) result
FROM
orderdetails
WHERE
productCode = 'S1_20';
6) MySQL SUM()
with join example
See the following orders
and orderdetails
tables:
You can use the SUM()
function in a SELECT
with JOIN
clause to calculate the sum of values in a table based on a condition specified by the values in another table.
This statement uses the SUM()
function to calculate the total amounts of the canceled orders:
SELECT
SUM(quantityOrdered * priceEach) cancelled_amount
FROM
orderdetails
INNER JOIN orders USING (orderNumber)
WHERE
status = 'Cancelled';
7) MySQL SUM IF example
The following statement uses the SUM()
function to calculate the number of items sold for each order status:
SELECT
status,
SUM(quantityOrdered)
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
GROUP BY status;
If you want to rotate rows to columns, you can use the SUM()
function with CASE
expression. It is kind of SUMIF
logic:
SELECT
SUM(CASE
WHEN status = 'Shipped' THEN quantityOrdered
END) qty_shipped,
SUM(CASE
WHEN status = 'Resolved' THEN quantityOrdered
END) qty_resolved,
SUM(CASE
WHEN status = 'Cancelled' THEN quantityOrdered
END) qty_cancelled,
SUM(CASE
WHEN status = 'On Hold' THEN quantityOrdered
END) qty_on_hold,
SUM(CASE
WHEN status = 'Disputed' THEN quantityOrdered
END) qty_on_disputed,
SUM(CASE
WHEN status = 'In Process' THEN quantityOrdered
END) qty_in_process
FROM
orderdetails
INNER JOIN
orders USING (orderNumber);
In this tutorial, you have learned how to use the MySQL SUM()
function to calculate the sum of a set of values.
0 Comments
CAN FEEDBACK
Emoji