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.
 

 

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$).
 

 

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.

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More