The COUNT() aggregate functions counts and displays the total number of entries. For example, to count the total number of entries in the table, issue the command below.
select COUNT(*) from employee_data;
+———-+
| COUNT(*) |
+———-+
| 21 |
+———-+
1 row in set (0.00 sec)
As you have learned, the * sign means "all data"
Now, let's count the total number of employees who hold the "Programmer" title.
select COUNT(*) from employee_data
where title = 'Programmer';
+———-+
| COUNT(*) |
+———-+
| 4 |
+———-+
1 row in set (0.01 sec)
The MySQL GROUP BY clause
The GROUP BY clause allows us to group similar data. Thus, to list all unique titles in our table we can issue
select title from employee_data
GROUP BY title;
+—————————-+
| title |
+—————————-+
| CEO |
| Customer Service Manager |
| Finance Manager |
| Marketing Executive |
| Multimedia Programmer |
| Programmer |
| Senior Marketing Executive |
| Senior Programmer |
| Senior Web Designer |
| System Administrator |
| Web Designer |
+—————————-+
11 rows in set (0.01 sec)
You'll notice that this is similar to the usage of DISTINCT, which we encountered in a previous session.
Okay, here is how you can count the number of employees with different titles.
select title, count(*)
from employee_data GROUP BY title;
+—————————-+———-+
| title | count(*) |
+—————————-+———-+
| CEO | 1 |
| Customer Service Manager | 1 |
| Finance Manager | 1 |
| Marketing Executive | 3 |
| Multimedia Programmer | 3 |
| Programmer | 4 |
| Senior Marketing Executive | 1 |
| Senior Programmer | 2 |
| Senior Web Designer | 1 |
| System Administrator | 2 |
| Web Designer | 2 |
+—————————-+———-+
11 rows in set (0.00 sec)
For the command above, MySQL first groups different titles and then executes count on each group.
Sorting the data in MySQL
Now, let's find and list the number of employees holding different titles and sort them using ORDER BY.
select title, count(*) AS Number
from employee_data
GROUP BY title
ORDER BY Number;
+—————————-+——–+
| title | Number |
+—————————-+——–+
| CEO | 1 |
| Customer Service Manager | 1 |
| Finance Manager | 1 |
| Senior Marketing Executive | 1 |
| Senior Web Designer | 1 |
| Senior Programmer | 2 |
| System Administrator | 2 |
| Web Designer | 2 |
| Marketing Executive | 3 |
| Multimedia Programmer | 3 |
| Programmer | 4 |
+—————————-+——–+
11 rows in set (0.00 sec)
Assignment:
1.Count the number of employees who have been with Bignet for four or more years.
2.Count employees based on their ages.
3.Modify the above so that the ages are listed in a descending order.
4.Find the average age of employees in different departments (titles).
5.Change the above statement so that the data is displayed in a descending order of average ages.
6.Find and list the percentage perk (perk/salary X 100) for each employee with the % perks sorted in a descending order.
Possible Answers:
1.mysql> select count(*) from employee_data
-> where yos >= 4;
+———-+
| count(*) |
+———-+
| 5 |
+———-+
1 row in set (0.01 sec)
2.mysql> select age, count(*)
-> from employee_data
-> GROUP BY age;
+——+———-+
| age | count(*) |
+——+———-+
| 25 | 1 |
| 26 | 1 |
| 27 | 3 |
| 28 | 1 |
| 30 | 1 |
| 31 | 1 |
| 32 | 6 |
| 33 | 1 |
| 34 | 2 |
| 35 | 1 |
| 36 | 2 |
| 43 | 1 |
+——+———-+
12 rows in set (0.00 sec)
3.mysql> select age, count(*)
-> from employee_data
-> GROUP BY age
-> ORDER by age DESC;
+——+———-+
| age | count(*) |
+——+———-+
| 43 | 1 |
| 36 | 2 |
| 35 | 1 |
| 34 | 2 |
| 33 | 1 |
| 32 | 6 |
| 31 | 1 |
| 30 | 1 |
| 28 | 1 |
| 27 | 3 |
| 26 | 1 |
| 25 | 1 |
+——+———-+
12 rows in set (0.00 sec)
4.mysql> select title, AVG(age)
-> from employee_data
-> GROUP BY title;
+—————————-+———-+
| title | AVG(age) |
+—————————-+———-+
| CEO | 28.0000 |
| Customer Service Manager | 32.0000 |
| Finance Manager | 36.0000 |
| Marketing Executive | 28.0000 |
| Multimedia Programmer | 36.0000 |
| Programmer | 30.5000 |
| Senior Marketing Executive | 36.0000 |
| Senior Programmer | 32.0000 |
| Senior Web Designer | 32.0000 |
| System Administrator | 34.5000 |
| Web Designer | 26.5000 |
+—————————-+———-+
11 rows in set (0.00 sec)
5.mysql> select title, AVG(age)
-> AS 'Average Age'
-> from employee_data
-> GROUP BY title
-> ORDER BY 'Average Age' DESC;
+—————————-+————-+
| title | Average Age |
+—————————-+————-+
| Finance Manager | 36.0000 |
| Multimedia Programmer | 36.0000 |
| Senior Marketing Executive | 36.0000 |
| System Administrator | 34.5000 |
| Customer Service Manager | 32.0000 |
| Senior Programmer | 32.0000 |
| Senior Web Designer | 32.0000 |
| Programmer | 30.5000 |
| CEO | 28.0000 |
| Marketing Executive | 28.0000 |
| Web Designer | 26.5000 |
+—————————-+————-+
11 rows in set (0.01 sec)
Note: We need to give a pseudoname to the column that contains the average ages so that we can sort it.
6.mysql> select f_name, l_name,
-> ((perks/salary) * 100) as '% Perk'
-> from employee_data
-> ORDER BY '% Perk' DESC;
+———+————+——–+
| f_name | l_name | % Perk |
+———+————+——–+
| Monica | Sehgal | 27.78 |
| Hal | Simlai | 25.71 |
| Manish | Sharma | 25.00 |
| Joseph | Irvine | 25.00 |
| Mike | Harper | 23.33 |
| John | Hagan | 20.83 |
| Peter | Champion | 20.83 |
| Fred | Kruger | 20.00 |
| John | MacFarland | 20.00 |
| Arthur | Hoopla | 20.00 |
| Edward | Sakamuro | 18.67 |
| Ganesh | Pillai | 18.18 |
| Kim | Hunter | 18.18 |
| Mary | Anchor | 17.65 |
| Anamika | Pandit | 16.67 |
| Hassan | Rajabi | 16.67 |
| Alok | Nanda | 14.29 |
| Paul | Simon | 14.12 |
| Danny | Gibson | 13.33 |
| Roger | Lewis | 13.00 |
| Shahida | Ali | 12.86 |
+———+————+——–+
21 rows in set (0.00 sec)