MySQL Tutorial - SUM() Function

The MySQL SUM() function is used to calculate the sum of values in a given column of a table. 

It takes a single argument, which is the name of the column or expression that you want to sum.

Here's an example query that uses the SUM() function: 

SELECT SUM(price) AS TotalPrice FROM Stores;

The resulting sum is assigned an alias "TotalPrice" using the AS keyword. The query returns a single row with a single column "TotalPrice" which contains the sum of all values in the "price" column of the "Stores" table. 

Initial situation in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

SUM function example:

mysql> SELECT SUM(available) AS InALLStores FROm Stores;
+-------------+
| InALLStores |
+-------------+
|        2633 |
+-------------+
1 row in set (0.00 sec)

mysql>

This MySQL query uses the SUM() function to calculate the total sum of the "available" column in the "Stores" table. 

The result of the SUM() function is assigned an alias "InALLStores" using the AS keyword.

The value returned in this case is 2633, which means that the total sum of all values in the "available" column of the "Stores" table is 2633. 

We can combine SUM and WHERE functions:

mysql> SELECT SUM(available) AS AllGoldPSUs FROM Stores WHERE product='GOLD PSU';
+-------------+
| AllGoldPSUs |
+-------------+
|         228 |
+-------------+
1 row in set (0.00 sec)

mysql>

Our query returns a single row with a single column "AllGoldPSUs" which contains the total sum of all values in the "available" column of the "Stores" table where the "product" column equals 'GOLD PSU'. 

Combination of SUM, WHERE and LIKE functions:

mysql> SELECT SUM(available) AS AllPSUs FROM Stores WHERE product LIKE "%PSU%";
+---------+
| AllPSUs |
+---------+
|     278 |
+---------+
1 row in set (0.00 sec)

mysql>

Here we use the SUM() function with a WHERE clause to calculate the total sum of the "available" column in the "Stores" table where the "product" column contains the string 'PSU'. 

The % symbol is a wildcard character used to match any sequence of characters, so the LIKE "%PSU%" condition matches any value in the "product" column that contains the string "PSU".

The result of the SUM() function is assigned an alias "AllPSUs" using the AS keyword.