MySQL SUM() function

Last update on August 19 2022 21:50:42 (UTC/GMT +8 hours)

SUM() function

MySQL SUM() function returns the sum of an expression. SUM() function returns NULL when the return set has no rows.

Syntax:

SUM([DISTINCT] expr)

Where expr is an expression.

The DISTINCT keyword can be used to sum only the distinct values of expr.

MySQL Version: 5.6

Contents:

Example : MySQL SUM() function
MySQL SUM() function with where clause and example
MySQL SUM() function using multiple columns and example
MySQL SUM() function with COUNT() function and variables and example
MySQL SUM() function with DISTINCT clause and example

Example: MySQL SUM() function

The following MySQL statement returns the sum of 'total_cost' from purchase table.

Sample table: purchase

Code:

SELECT SUM(total_cost)             
FROM purchase;

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

mysql> SELECT SUM(total_cost)             
    -> FROM purchase;
+-----------------+
| SUM(total_cost) |
+-----------------+
|         3590.00 | 
+-----------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>

View the example in browser

JSP script:

<%@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>

Example: MySQL SUM() function with where clause

MySQL SUM() function with WHERE retrieves the sum of a given expression which is filtered against a condition placed after WHERE. The following MySQL statement returns the sum of 'total_cost' from purchase table for the category ('cate_id') given with WHERE clause.

Sample table: purchase

Code:

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

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

mysql> SELECT SUM(total_cost) 
    -> FROM purchase          
    -> WHERE cate_id='CA001';
+-----------------+
| SUM(total_cost) |
+-----------------+
|         1725.00 | 
+-----------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>

View the example in browser

Example: MySQL SUM() function using multiple columns

MySQL SUM() function retrieves the sum value of an expression which is made up of more than one columns. The above MySQL statement returns the sum of multiplication of 'receive_qty' and 'purch_price' from purchase table for each group of category ('cate_id') .

Sample table: purchase

Code:

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

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

mysql> SELECT cate_id,
    -> SUM(receive_qty*purch_price)
    -> FROM purchase            
    -> GROUP BY cate_id;
+---------+------------------------------+
| cate_id | SUM(receive_qty*purch_price) |
+---------+------------------------------+
| CA001   |                      1725.00 | 
| CA002   |                       965.00 | 
| CA003   |                       900.00 | 
+---------+------------------------------+
3 rows in set (0.02 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>

View the example in browser

Example: MySQL SUM() function with COUNT() function and variables

The following MySQL statement will return the sum of the ‘mysum’, a temporary variable which counts number of books containing more than 200 pages from 'book_mast' table.

Sample table: book_mast

Code:

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

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

mysql> SELECT SUM(mysum) 
    -> FROM( 
    -> SELECT COUNT(*) AS  mysum 
    -> FROM book_mast
    -> WHERE no_page>200) AS bb;
+------------+
| SUM(mysum) |
+------------+
|         12 | 
+------------+
1 row in set (0.02 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>

View the example in browser

Example: MySQL SUM() function with DISTINCT clause

MySQL SUM() function retrieves the sum of a unique value of an expression if it is accompanied by DISTINCT clause. The following MySQL statement returns the sum of a number of branches ('no_of_branch') from publisher table, where, if more than one publisher has the same number of branches, that number (i.e. number of branches) is taken once only.

Sample table: publisher

Code:

SELECT SUM(DISTINCT no_of_branch)          
FROM publisher;

Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:

mysql> SELECT SUM(DISTINCT no_of_branch)          
    -> FROM publisher;
+----------------------------+
| SUM(DISTINCT no_of_branch) |
+----------------------------+
|                         64 | 
+----------------------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>

View the example in browser

Online Practice Editor:

Previous: STDDEV()
Next: SUM() with group by