If your occupation has anything to do with databases, you face all kinds of routine tasks on data manipulation every day. Does it mean you have to do them all manually? Of course, not. It would consume all your time and raise plenty of risks. There are many convenient IDEs like dbForge Studio for MySQL with rich functionality letting you do the tasks automatically. No matter which tool you use, practical methods do exist, and they are well-known to database professionals. This article will explore one of the most frequently used functions in MySQL – the COUNT() function.
Contents
- What is MySQL COUNT Function?
- MySQL COUNT syntax example
- How to use the COUNT function
- An example of using the COUNT function
- Using COUNT with the WHERE clause example
- An example of using COUNT with GROUP BY
- The usage of COUNT with HAVING with an example
- Conclusion
What is MySQL COUNT Function?
Let’s start with a simple question. What does the COUNT function do and why do we need it? The answer is pretty simple: it counts records. You might need to count the employees in MySQL databases, check the number of orders with different statuses, correlate revenues and expenses, etc. In all such cases (and countless other situations) you access a database, make a request, and get the result to ground your further analysis on.
Understanding the COUNT function and using it in practice are crucial for MySQL professionals. Though many modern tools let us perform counting operations even without manual coding, we still need to know what the essence of the command is, how the method works, and how to use it correctly.
MySQL COUNT syntax example
MySQL COUNT function is an in-built aggregate function that counts values in the query results and returns the total number. It can count all rows or some of them, identifying the records matching specific conditions. This function is available in MySQL starting from early versions like version 4.0.
When you use this function in a query, it looks as follows:
COUNT(expression)As a part of the SELECT statement, the syntax mostly looks as below:
SELECT COUNT(expression)
FROM table
WHERE condition;- Expression is the parameter where you specify which values you want to count. It can be a field or string type value.
- Table is the table you use as a source to fetch records. You need to have at least one table.
- WHERE is an optional condition for selecting records from the table.
How to use the COUNT function
In this article, we are going to demonstrate how to use different manifestations of the COUNT function in dbForge Studio for MySQL. It is a full-fledged MySQL GUI that helps create and execute queries, develop and debug stored routines, automate database object management, compare and synchronize databases, analyze table data, and much more. Its rich functionality is delivered under an intuitive interface.
To begin your dbForge Studio journey, you need to create a connection:
1. Click New Connection in the Database menu.
2. Fill in the corresponding database connection properties and click Test Connection.
3. If everything is set correctly, you will see the Successfully connected message. Click Connect.
4. Once the connection is up, click New SQL.
5. A blank SQL window opens. Here, you can type in and execute any query. That is exactly what we are going to do further in this article.
An example of using the COUNT function
COUNT
In practice, we work with three types of this function:
COUNT(*)COUNT(expression)COUNT(distinct)
COUNT(*) is a variant counting all the results returned by SELECT, including NULLs and duplicates.
As an example, let’s take the sakila database that contains the film table. We are going to find out how many new movies were released in 2012. The command looks as follows:
SELECT COUNT(*)
FROM film
WHERE release_year = 2012;To execute the query, paste it into the SQL window and either click 
, or press F5.
COUNT(expression)
COUNT(expression) is the most common method we use to count records. The expression plays the same role as the WHERE clause above – it defines the condition for the data to be returned.
However, unlike COUNT(*), COUNT(expression) does not count NULL values at all – such rows are ignored.
Let’s take look at another example. Assume that our sakila database includes a table called film with such columns:
| film_id | title | release_year | description |
| 1 | The Green Mile | 1999 | A magical realism drama film written and directed by Frank Darabont and based on Stephen King’s 1996 novel of the same name. |
| 2 | Pulp Fiction | 1994 | A black comedy crime film written and directed by Quentin Tarantino, who conceived it with Roger Avary. |
| 3 | Forrest Gump | 1994 | A comedy-drama film directed by Robert Zemeckis and written by Eric Roth. |
| 4 | Spirited Away | 2001 |
Suppose we are going to count the number of movies that have a description using the SELECT COUNT option:
SELECT COUNT(description)
FROM film;
The command returned only 3 entities because the 4th one does not have any value in the description column. That value is NULL, and MySQL count column values only works when those values are not NULLs.
COUNT(DISTINCT expression)
COUNT(DISTINCT expression) is the method returning all the unique, non-NULL values. The syntax matches the following pattern:
SELECT COUNT(DISTINCT column)
FROM table;Now, we are going to find out how many unique titles belonging to the horror genre we have. There, we’ll apply MySQL COUNT(distinct):
SELECT COUNT(DISTINCT 'Horror')
FROM film;
Now, let us investigate the usage of COUNT() with different clauses.
Using COUNT with the WHERE clause example
Earlier, we used WHERE in one of our examples. We can use this clause to set the condition for the records to fetch. The common scenario of using this clause is with SELECT COUNT(*) when we want to know the number of all records, including those with NULLs.
Now, we are going to find out how many movies with exclusive deleted scenes are there. Thus, we’ll apply the SELECT COUNT combination:
SELECT COUNT(*)
FROM film
WHERE special_features = 'Deleted Scenes';
This command will count all the movies that include the deleted scenes. This number will include the records that only have this type of special feature, those containing multiple ones, and those having NULLs.
We can apply an alias to the expression to make the results more suitable for reading:
SELECT COUNT(*) AS "Trailers"
FROM film
WHERE release_year < 2020;
When this query is executed, the system will display Trailers as the field name for the number of movies released before 2020.
Note: Adding external clauses to the COUNT(*) function can create additional load for the performance, so consider this circumstance.
An example of using COUNT with GROUP BY
The COUNT() function can be combined with GROUP BY. The purpose is simple – to improve the readability of the results. This clause lets us categorize the returned MySQL data by assigning the results to different groups.
Suppose we need to count the number of movies released in a particular year. We use the below code containing the MySQL COUNT GROUP BY combination:
SELECT release_year, COUNT(*)AS "Number"
FROM film
GROUP BY release_year;
The usage of COUNT with HAVING with an example
The HAVING clause can also be used with the COUNT function. It comes in combination with GROUP BY – it is a mandatory condition, as HAVING does not work separately.
When the script contains COUNT, GROUP BY, and HAVING, the system starts with grouping rows and then applies COUNT() with HAVING.
For instance, we want to count the ratings that contain more than a hundred movies. This is where we are using COUNT() with both GROUP BY and HAVING clauses:
SELECT rating, COUNT(*)
FROM film
GROUP BY rating
HAVING COUNT(*) >=100;
Learn more about MySQL GROUP BY statement in another blog article of ours.
Conclusion
Whether you are a true code lover or need the basics of MySQL only for some practical routines at work, the COUNT function is the thing you can’t ignore. It seems simple – it is all about the basic task where MySQL counts rows: all of them or just separate ones. However, it is essential and, in many cases, indispensable.
The COUNT function also allows us to filter the data. We can use various clauses to define our needs. WHERE, GROUP BY, and HAVING are the most common ones. By applying such clauses, we specify the target data efficiently, group the results, and order the data to extract the required value from the numbers.
This function is simple in syntax and use (though codes containing it can be very complex), and it is handy at work. Apply it appropriately, and you will get the job done!
