Free SQL tutorial – Finding the average and sum

Totalling column values with MySQL SUM

The SUM() aggregate function calculates the total of values in a column. You require to give the column name, which should be placed inside parenthesis.
Let’s see how much Bignet spends on salaries.

select SUM(salary) from employee_data;

+-------------+
| SUM(salary) |
+-------------+
|     1997000 |
+-------------+
1 row in set (0.00 sec)

SImilarly, we can display the total perks given to employees.

select SUM(perks) from employee_data;

+------------+
| SUM(perks) |
+------------+
|     390000 |
+------------+
1 row in set (0.00 sec)

How about finding the total of salaries and perks?

select sum(salary) + sum(perks) from employee_data;

+-------------------------+
| sum(salary)+ sum(perks) |
+-------------------------+
|                 2387000 |
+-------------------------+
1 row in set (0.01 sec)

This shows a hidden gem of the SELECT command. You can add, subtract, multiply or divide values. Actually, you can write full blown arithemetic expressions. Cool!

MySQL AVG() – Finding the Average

The AVG() aggregate function is employed for calculating averages of data in columns.

select avg(age) from employee_data;
+----------+
| avg(age) |
+----------+
|  31.6190 |
+----------+
1 row in set (0.00 sec)

This displays the average age of employees in Bignet and the following displays the average salary.

select avg(salary) from employee_data;
+-------------+
| avg(salary) |
+-------------+
|  95095.2381 |
+-------------+
1 row in set (0.00 sec)

Your comments

IMPORTANT Have a question / problem? Click here to ask an expert.