![]() ![]() In this tutorial, you have learned how to use the MySQL SUM() function to calculate the sum of a set of values.Both of these will produce the right values: select distributor_id, SUM( CASE WHEN status = 'In Process' THEN quantityOrdered SUM( CASE WHEN status = 'Disputed' THEN quantityOrdered SUM( CASE WHEN status = 'On Hold' THEN quantityOrdered SUM( CASE WHEN status = 'Cancelled' THEN quantityOrdered SUM( CASE WHEN status = 'Resolved' THEN quantityOrdered It is kind of SUMIF logic: SELECT SUM( CASE WHEN status = 'Shipped' THEN quantityOrdered ![]() ![]() If you want to rotate rows to columns, you can use the SUM() function with CASE expression. The following statement uses the SUM() function to calculate the number of items sold for each order status: SELECT status, WHERE status = 'Cancelled' Code language: SQL (Structured Query Language) ( sql ) 7) MySQL SUM IF example This statement uses the SUM() function to calculate the total amounts of the canceled orders: SELECT SUM(quantityOrdered * priceEach) cancelled_amount 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. See the following orders and orderdetails tables: ProductCode = 'S1_20' Code language: SQL (Structured Query Language) ( sql ) 6) MySQL SUM() with join example See the following query: SELECT COALESCE( SUM(quantityOrdered * priceEach), 0) result FROM The COALESCE function accepts two arguments and returns the second argument if the first argument is NULL otherwise, it returns the first argument. In this case, you can use the COALESCE() function. Sometimes, you may want the SUM() function to return zero instead of NULL. The SUM() function returns NULL if the result set is empty. OrderTotal Code language: SQL (Structured Query Language) ( sql ) 5) MySQL SUM() with NULL example HAVING SUM(quantityOrdered * priceEach) > 60000 ORDER BY This example illustrates how to select orders whose order amounts are greater than 60,000. You can use the SUM() function in the HAVING clause to filter the group. ![]() The SUM() function calculates the total of each amount in each order.Ĥ) MySQL SUM() with HAVING clause example.The GROUP BY clause divides order details into groups grouped by the order number.OrderTotal DESC Code language: SQL (Structured Query Language) ( sql ) SUM(quantityOrdered * priceEach) orderTotal The SUM() function is often used with the GROUP BY clause to calculate the sum for each group.įor 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 In this tutorial, the SUM() function calculates the total of the following expression of all order line items of order number 10110: quantityOrdered * priceEach Code language: SQL (Structured Query Language) ( sql ) 3) MySQL SUM() with the GROUP BY clause example To calculate the total for the order number 10110, you use the SUM() function as follows: SELECT SUM(quantityOrdered * priceEach) orderTotal OrderNumber = 10100 Code language: SQL (Structured Query Language) ( sql ) The following shows the order line items of the order number 10110: SELECT Orderdetails Code language: SQL (Structured Query Language) ( sql ) 2) MySQL SUM() function with expression example This example uses the SUM() function to get the total number of items of the order details: SELECT SUM(quantityOrdered) SalesQuantity Let’s take a look at the table orderdetails in the sample database. In this case, the SUM() with the DISTINCT option only calculates the sum of distinct values which are 1, 2 and 3. And it ignores NULL.įinally, use the SUM() with the DISTINCT option to calculate the total values in the n column: SELECT SUM( DISTINCT n) Sum_demo Code language: SQL (Structured Query Language) ( sql )Īs you can see, the SUM() function calculates the total of 1, 1, 2, and 3. Third, use the SUM() function to calculate the total values in the n column: SELECT SUM(n) Then, insert some rows into the sum_demo table: INSERT INTO sum_demo(n)Ĭode language: SQL (Structured Query Language) ( sql ) ) Code language: SQL (Structured Query Language) ( sql ) The SUM() function ignores the NULL values in the calculation.įirst, create a new table named sum_demo: CREATE TABLE sum_demo (.The DISTINCT option instructs the SUM() function to calculate the sum of only distinct values in a set.If you use the SUM() function in a SELECT statement that returns no row, the SUM() function returns NULL, not zero.The syntax of the SUM() function is as follows: SUM(DISTINCT expression) Code language: SQL (Structured Query Language) ( sql ) The SUM() function is an aggregate function that allows you to calculate the sum of values in a set. Summary: in this tutorial, you will learn how to use the MySQL SUM() function to calculate the sum of values in a set. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |