MySQL SUM

MySQL SUM

 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 a SELECT a statement that returns no row, the SUM() function returns NULL, not zero.
  • The DISTINCT option instructs the SUM() function to calculate the sum of only distinct values in a set.
  • The SUM() function ignores the NULL 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.

Reactions

Post a Comment

0 Comments

close