MySQL Select SUM() with Example

In SQL language, the SUM() aggregation function allows calculating the total sum of a column containing numerical values. This function only works on columns of numeric types (INT, FLOAT …) and does not sum NULL values.
 

Syntax:

Here is the syntax to use this SQL function:

SELECT SUM(column_name) FROM table

This SQL query allows to calculate the sum of the values contained in the column “column_name”.

Note: It is possible to filter the records with WHERE command to calculate the sum of the desired elements only.
 
[st_adsense]  

Example :

Let’s suppose a system that manages invoices and records each purchase in a database. This system uses an “invoice” table containing one row for each product. The table looks like the example below:

+------+----------+------------+-------+---------+
|  id  |   name   | invoice_id | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | Monitor  | 1          |  45   | 100     |
|  102 | CD-ROM   | 1          |  100  | 40      |
|  103 | Modem    | 1          |  12   | 20      |
|  104 | Speaker  | 2          |  11   | 3       |
|  105 | Pencil   | 3          |  148  | 2       |
|  106 | Gum      | 4          |  250  | 1       |
+------+----------+------------+-------+---------+

Sum function with WHERE:

To calculate the sum of the invoice n°1 it is possible to use the following SQL query:

SELECT SUM(price) AS total_price
FROM invoice
WHERE invoice_id = 1

Output:

160

This result shows that all the items purchased on invoice n°1 represent an amount of 160$ (sum of 100$ + 40$ + 20$).
 
[st_adsense]  

Sum with GROUP BY

To calculate the sum of each invoice, it is possible to group the rows based on the “invoice_id” column. Such a result can be achieved by using the following query:

SELECT invoice_id, SUM(price) AS total_price
FROM invoice
GROUP BY invoice_id

Output:

+---------------+-------------+
|  invoice_id   | total_price |
+---------------+-------------+
|   1           | 160         |
|   2           | 3           |
|   3           | 2           |
|   4           | 1           |
+---------------+-------------+

This result shows that it is possible to determine the total price of each invoice using the SUM() function.