Mysql как найти сумму

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() function is an aggregate function that allows you to calculate the sum of values in a set. The syntax of the SUM() function is as follows:

SUM(DISTINCT expression)Code language: SQL (Structured Query Language) (sql)

Here is how the SUM() function works:

• If you use the SUM() function in a SELECT 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
);Code language: SQL (Structured Query Language) (sql)

Then, insert some rows into the sum_demo table:

INSERT INTO sum_demo(n) 
VALUES(1),(1),(2),(NULL),(3);
Code language: SQL (Structured Query Language) (sql)

Third, use the SUM() function to calculate the total values in the n column:

SELECT 
    SUM(n)
FROM
    sum_demo;Code language: SQL (Structured Query Language) (sql)

As you can see, the SUM() 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;Code language: SQL (Structured Query Language) (sql)

In this case, the SUM() with the DISTINCT 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;Code language: SQL (Structured Query Language) (sql)

2) MySQL SUM() function with expression example

The following shows the order line items of the order number 10110:

SELECT 
    orderNumber, 
    quantityOrdered, 
    priceEach
FROM
    orderdetails
WHERE
    orderNumber = 10100;Code language: SQL (Structured Query Language) (sql)

To calculate the total for the order number 10110, you use the SUM() function as follows:

SELECT 
	SUM(quantityOrdered * priceEach)  orderTotal
FROM
	orderdetails
WHERE
	orderNumber = 10100;Code language: SQL (Structured Query Language) (sql)

In this tutorial, the SUM() function calculates the total of the following expression of all order line items of the order number 10110:

quantityOrdered * priceEachCode language: SQL (Structured Query Language) (sql)

3) MySQL SUM() with the GROUP BY clause example

The SUM() 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;Code language: SQL (Structured Query Language) (sql)

In this example:

• The GROUP BY clause divides order details into groups grouped by the order number.
• The SUM() 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;Code language: SQL (Structured Query Language) (sql)

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 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';Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

If you want to rotate rows to columns, you can use the SUM() fnction 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);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MySQL SUM() function to calculate the sum of a set of values.

script1adsense2code

script1adsense3code

 SUM ([DISTINCT] expr)

Содержание:

• Пример: функция MySQL SUM ()
• Функция MySQL SUM () с предложением и примером where
• MySQL SUM () функция, используя несколько столбцов и пример
• MySQL SUM () функция с функцией COUNT () и переменными и пример
• MySQL SUM () функция с предложением и примером DISTINCT

SELECT SUM(total_cost)             
FROM purchase;

 mysql> SELECT SUM (total_cost)             
    -> С покупки;
+ ----------------- +
| SUM (total_cost) |
+ ----------------- +
| 3590,00 | 
+ ----------------- +
1 ряд в наборе (0,00 сек)

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-SUM()- php MySQL examples | w3resource</title>
<meta name="description" content="example-SUM()- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Sum of total costs of purchases:</h2>
<table class='table table-bordered'>
<tr>
<th>Sum of total costs of purchases</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(total_cost) 
FROM purchase') as $row) {
echo "<tr>";
echo "<td>" . $row['SUM(total_cost)'] . "</td>";
echo "</tr>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-sum()</title>
</head>
<body>
<%
try {
Class.forName("com.MySQL.jdbc.Driver").newInstance();
String Host ="jdbc:MySQL://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT SUM(total_cost) FROM purchase";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Sum of total costs of purchases</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("SUM(total_cost)")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

SELECT SUM(total_cost) 
FROM purchase          
WHERE cate_id='CA001';

 mysql> SELECT SUM (total_cost) 
    -> ОТ покупки          
    -> ГДЕ cate_id = 'CA001';
+ ----------------- +
| SUM (total_cost) |
+ ----------------- +
| 1725,00 | 
+ ----------------- +
1 ряд в наборе (0,00 сек)

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-aggregate-functions-and-grouping-sum-with-where- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-sum-with-where- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Sum of total costs of purchases for category id CA001:</h2>
<table class='table table-bordered'>
<tr>
<th>Sum of total costs of purchases for category id CA001</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(total_cost) 
FROM purchase
WHERE cate_id="CA001"') as $row) {
echo "<tr>";
echo "<td>" . $row['SUM(total_cost)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

SELECT cate_id,
SUM(receive_qty*purch_price)
FROM purchase            
GROUP BY cate_id;

 mysql> SELECT cate_id,
    -> СУММА (receive_qty * purchase_price)
    -> ОТ покупки            
    -> GROUP BY cate_id;
+ --------- + ------------------------------ +
| cate_id | SUM (receive_qty * purchase_price) |
+ --------- + ------------------------------ +
| CA001 | 1725,00 | 
| CA002 | 965,00 | 
| CA003 | 900,00 | 
+ --------- + ------------------------------ +
3 ряда в наборе (0,02 сек)

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-aggregate-functions-and-grouping-sum-using-multiple-columns- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-sum-using-multiple-columns- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Category id and Sum of product of received quantity and purchase price:</h2>
<table class='table table-bordered'>
<tr> 
<th>Category id</th><th>Sum of product of received quantity and purchase price</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password"; 
$db = "your_dbname"; 
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT cate_id,SUM(receive_qty*purch_price) 
FROM purchase
GROUP BY cate_id') as $row) {
echo "<tr>";
echo "<td>" . $row['cate_id'] . "</td>";
echo "<td>" . $row['SUM(receive_qty*purch_price)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

SELECT SUM(mysum) 
FROM( 
SELECT COUNT(*) AS  mysum 
FROM book_mast
WHERE no_page>200) AS bb;

 mysql> ВЫБЕРИТЕ СУММУ (mysum) 
    -> ОТ ( 
    -> ВЫБЕРИТЕ СЧЕТ (*) AS mysum 
    -> ОТ book_mast
    -> ГДЕ no_page> 200) AS bb;
+ ------------ +
| СУММ (mysum) |
+ ------------ +
| 12 | 
+ ------------ +
1 ряд в наборе (0,02 сек)

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-aggregate-functions-and-grouping-sum-with-count-and-variables- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-sum-with-count-and-variables- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Returning the sum of the ‘mysum’ a temporary variable  which is used to count number of books containing more than 200 pages:</h2>
<table class='table table-bordered'>
<tr> 
<th>SUM(mysum)</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(mysum) 
FROM( 
SELECT COUNT(*) AS  mysum 
FROM book_mast where no_page>200) AS bb') as $row) {
echo "<tr>";  
echo "<td>" . $row['SUM(mysum)'] . "</td>";  
echo "</tr>";  
}
?> </tbody></table> 
</div> 
</div> 
</div> 
</body>
</html>

SELECT SUM(DISTINCT no_of_branch)          
FROM publisher;

 mysql> SELECT SUM (DISTINCT no_of_branch)          
    -> ОТ издателя;
+ ---------------------------- +
| SUM (DISTINCT no_of_branch) |
+ ---------------------------- +
| 64 | 
+ ---------------------------- +
1 ряд в наборе (0,00 сек)

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-aggregate-functions-and-grouping-sum-with-distinct- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-sum-with-distinct- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Sum of unique number of branches of publishers:</h2>
<table class='table table-bordered'>
<tr>
<th>Sum of unique number of branches of publishers</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(DISTINCT no_of_branch) 
FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['SUM(DISTINCT no_of_branch)'] . "</td>"; 
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

Introduction to MySQL sum()

• MySQL SUM() is a MySQL aggregate function that calculates the sum of provided set of values. The SUM() avoids the NULL values while evaluating the sum of the data values in MySQL.
• As it is an aggregate function so it implements the sum calculation on multiple values and produces a distinct value.In MySQL, the aggregate functions are normally applied in combination with the GROUP BY clause so that is calculates an aggregate result value for the specific values or group of values mentioned in the query.
• If we are finding the sum of a specific expression, then it may give NULL value when there exists no rows in the result set.

Syntax

We have the subsequent elementary syntax structure for the MySQL SUM() function:

SUM(Expr_Value)

Or

SUM(DISTINCT Expr_Value)

Here, let us explain the syntax terms:

• When the SELECT statement is used with SUM() function that outputs no row, then the MySQL SUM() function will return NULL value but not zero.
• The option DISTINCT keyword is provided to instruct the MySQL SUM() function to evaluate the total of unique values in the set.
• The MySQL SUM() function disregards the NULL values in the sum calculation in the server.
• Expr_Value can be any column name of the specific table. It can be one or multiple separated by comma.

How SUM() function works in MySQL?

The MySQL SUM() function is implemented with SELECT statement and  also can be calculated with JOIN clauses.

• We use the MySQL SUM() function along with WHERE clause also to retrieve the result sum of a particular expression that is clarified against a condition positioned after the clause WHERE. This will be helpful to find the sum total of values from a large set of data in the database.
• Suppose, if we need to rotate table rows to table columns then, the SUM() function is used together with the MySQL CASE expression which is a type of SUMIF logic illustrated below:

SELECT
SUM(CASE
WHEN expression…
END) ColumnName1,
SUM(CASE
WHEN expression…
END) ColumnName2,…..,
FROM TableName1 INNER JOIN TableName2 USING(ColumnName);

Here, the syntax shows a CASE statements with SUM() function evaluated for table columns done for the respective tables. The tables are also joined with INNER JOIN and applying the USING keyword to specifycolumn name of the table.

Since, the SUM() is an aggregate function so it works on multiple table rows or of a table column to find the sum result to return only a value.

Examples to Implement MySQL sum()

Let us evaluate the MySQL SUM() to show the working of sum with the table columns and rows to get the result total set.

Example #1. Simple Example of MySQL SUM() function

Let us take a table from the database as a demo table to generate the total sum of column table values.

We have a table named Books having some fields defined with columns as BookID, BookName, Language and Price. To view the contents of this Books table let us query the data:

Code #1

SELECT * FROM Books;

Output:

We are going to use the aggregate SUM() function to fetch the total price of the books from the table quantity using the query below:

Code #2

SELECT SUM(Price)Total_Price FROM Books;

Output:

As shown above, the sum has calculated the sum of total price from the book column.

We can also apply the DISTINCT keyword to find out the unique sum value of unique column values. So, the SUM() function will ignore the duplicate values and NULL values if available in the column values of table.

Code #3

SELECT SUM(DISTINCT Price) Total_Price FROM Books;

Output:

It shows the same sum total because the Price column does not contain any duplicates.

Example #2. with expression

Suppose, we have a table named Suppliers:

Code #1

select * from suppliers;

Output:

For supplier line items of the supplier id 11we have the following query as:

Code #2

SELECT Category, Unit, CostEach FROM Suppliers WHERE Supplier_ID = 11;

Output:

Now, we will calculate the total for a supplier item with Supplier id 11 using MySQL SUM() function:

Code #3

SELECT SUM(Unit * CostEach)TotalCost FROM Suppliers WHERE Supplier_ID = 11;

Output:

Example #3. with GROUP BY clause and ORDER BY clause

We will use SUM() function with GROUP BY clause to produce the sum output by grouping the values based on a specific column of the table.For example, using the SUM() we will query the total price of each supplier itemwith GROUP BY clause:

Code #1

SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID ORDER BY TotalCost DESC;

Output:

Here the result set is grouped by Supplier id and ordered by total cost calculated in descending order.

Example #4. with clause HAVING

The HAVING clause is used with the SUM() function to filter the sum group where the total cost amount is greater than certain provided integer value as follows:

Code #1

SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID
HAVING SUM(Unit * CostEach)>200 ORDER BY TotalCost DESC;

Output:

Example #5. with NULL

If the result set is empty then the SUM() returns NULL value but you can also show zero instead using the COALESCE() with two arguments:

Code #1

SELECT COALESCE (SUM(Unit * CostEach), 0) Output FROM Suppliers WHERE Supplier_ID = 15;

Output:

The result is zero because there is no row with supplier id 15 in the table Suppliers.

Example #6. with JOIN clause

We will take two tables Products and Suppliers where:

Products – Code #1

select * from products;

Output:

Suppliers – Code #2

select * from suppliers;

Output:

Calculating sum by SUM() clause with JOIN clause as follows:

Code #3

SELECT SUM(Unit * CostEach) Product_cost FROM Suppliers INNER JOIN Products ON Supplier_ID WHERE Product_Name = 'Maggie';

Output:

The result sum value is evaluated on the basis of a condition provided by the values in the next table.

Conclusion

• The MySQL SUM() is similar to a mathematical sum calculation that finds out the totality of provided table values in database.
• The aggregate SUM() function is useful to get the summarized data result set of integer data type values especially for those column values of tables containing any product records or eCommerce or business related database.

Recommended Articles

This is a guide to MySQL sum(). Here we discuss an introduction to MySQL sum(), syntax, parameters, how does it work with query examples. You can also go through our other related articles to learn more –

1. MySQL GROUP_CONCAT()
2. MySQL ON DELETE CASCADE
3. MySQL REINDEX
4. MySQL LAG()