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)