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)
Assignments:
1.Display the sum of ages of employees working at Bignet.
2.How would you calculate the total of years of service the employees of Bignet have in the company?
3.Calculate the sum of salaries and the average age of employees who hold "Programmer" title.
4.What do you understand from the following statement?
select (SUM(perks)/SUM(salary) * 100)
from employee_data;
Possible Answers:
1.mysql> select SUM(age) from employee_data;
+———-+
| SUM(age) |
+———-+
| 664 |
+———-+
1 row in set (0.00 sec)
2.mysql> select SUM(yos) from employee_data;
+———-+
| SUM(yos) |
+———-+
| 56 |
+———-+
1 row in set (0.00 sec)
3.mysql> select SUM(salary), AVG(age)
-> from employee_data where
-> title = 'Programmer';
+————-+———-+
| SUM(salary) | AVG(age) |
+————-+———-+
| 300000 | 30.5000 |
+————-+———-+
1 row in set (0.00 sec)
4.This displays the percentage of salary that is given as perks to the employees of Bignet.
mysql> select (SUM(perks)/SUM(salary) * 100)
-> from employee_data;
+——————————–+
| (SUM(perks)/SUM(salary) * 100) |
+——————————–+
| 19.53 |
+——————————–+
1 row in set (0.00 sec)