MySQL SUM() function
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
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>
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>
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>
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>
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>
Online Practice Editor:
Previous:
STDDEV()
Next:
SUM() with group by
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises