WebDevelopersNotes logo

home-icon Home / MySQL / Online MySQL training course – Counting

Online MySQL training course – Counting

Online MySQL training course - Counting

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 learnt, 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)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

The Times New Roman typeface is probably based on a font designed by William Starling Burgess some 30 years before. Times New Roman was created in 1931 by Victor Larden under the guidance of Stanley Morison. The typeface was commissioned by The Times UK. They approached Morison who had voiced his criticism of the typeface the newspaper had been employing. The Times ended up using Times New Roman for 40 years straight. [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy