MySQL Count – The Ultimate Guide on MySQL Count() Function

Every database management system provides some functions to assist in the access and manipulation of the data it stores, and the MySQL database is not an exception. In this article we’re going to provide some guidance on the MySQL Count function Count() with some emphasis on its syntax, usage and parameter values.

Also, we’re going to look at some of the usage variations of MySQL Count along with some examples to assist you in gaining mastery of the Count() function. Without further delay – let’s go!

ALSO READ: SQL vs MySQL: Difference Between SQL and MySQL

What Is MySQL Count Function?

First, let’s start with some definition – What is MySQL Count function? MySQL Count() function is an inbuilt MySQL database aggregate function that allows you to count values returned by a database SQL query statement.

Therefore, Count() returns a count of the number of values in a given expression. And it enables you as a database developer to count rows – this includes all the rows as well as only those rows that match a condition you specify.

Syntax

What is the MySQL Count() function syntax? The syntax is pretty simple and straight-forward. It looks like this:

COUNT()
 
However, the function implements in three forms which are:
 
COUNT(*)
COUNT(expression)
COUNT(DISTINCT expression)
 
Usage
 
As already mentioned above, the MySQL Count function is an aggregate function which you can use to count the number of records returned by a given SQL query.
 
So for example, if you want to return the number of students in a “Student” table, the query will like this:
 
SELECT COUNT(StudentID) AS NumberOfStudents FROM Student;
 
Parameter Values
 
Furthemore, the Count() function take on different parameter values such as:
 
1. Expressions: Must be in the GROUP BY caluse at the end of the SQL query and are not encapsulated within the Count() function – expression1, expression2, expression3, ……expressionN
 
2. Aggregates: These are the expressions to be aggregated and can be a column in the database table who non-null values counted.
 
3. Tables: The Count() runs against a database table that you want to pull records. So there must be at least one table listed the FROM part of the SQL query construct.
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]
 
 
MySQL Count Function Variations
 
Now, let’s take a look at some of MySQL Count() function variations as well as some examples to help you gain some understanding of the concept.
 
MySQL Count Rows
 
As a matter of act, the purpose of having a database is to answer questions. For instance, you may want to answer the question, “how many products are remaining in stock?” or “how many students have a grade point average of 3.0 and above?”
 
So as you can see, the importance of being able to answer these questions is vital. Luckily, MySQL provides a way to do this through its Count() function.
 
So let’s say you want to return all rows for a given table in a MySQL database and count number of rows returned you’ll run the following query:
 
SELECT COUNT(*) FROM TableName;
 
Now, if there 20 rows of records in that table, you’ll get a returned value of:
 
 

+---------------------------+
|          COUNT(*)         |
+---------------------------+
|             20            |
+---------------------------+
 
 
MySQL Count Where
 
Next, let’s assume you want to specify a condition that the rows returned must meet, the following query will do the trick:
 
SELECT COUNT(*)
FROM TableName
WHERE ColumnName = 1
 
As you can see in the above statement, we added the WHERE to indicate that the rows returned must meet the condition of where the value of a specific ColumnName is 1.
 
So to put this in perspective, let’s say that you have a Student that has the following records:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
And you want to return all students whose country is “USA” and count the total number of students meeting such criteria; you’ll run the following query:
 
SELECT COUNT(*)
FROM Student
WHERE country = "USA";
 
When the query is executed, MySQL will return the following value:
 
 

+---------------------------+
|          COUNT(*)         |
+---------------------------+
|             2             |
+---------------------------+
 
 
And this is because there are two students in the table whose country is “USA.” So you get the idea.
 
MySQL Select Count
 
So as you can see, the MySQL Select Count function allows you to aggregate and return the count of the total number of records retrieved by a specific SQL query against a MySQL database. For example:
 
SELECT COUNT(*) FROM Student;
 
The above query will retrieve all records from the Student table and then return the total value of the number of records (i.e., rows) returned.
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]
 
 
MySQL Count Group By
 
Another significant variation of the MySQL Count() function is the MySQL Count Group By. Notably, you employ the GROUP BY when you want to group the values from a column of a table in a MySQL database.
 
Also, you can use it when performing calculations on that table’s column. Likewise, you can apply other function such as AVG, SUM, etc. to the grouped table column.
 
So here’s an example, let’s say we’re still working on our students table:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
And suppose we want to count the number of students in each country. Now, if we run the following SQL query:
 
SELECT COUNT(*) FROM Student;
 
It will return a result that looks like this:
 
 

+---------------------------+
|          COUNT(*)         |
+---------------------------+
|             5             |
+---------------------------+
 
 
However, this is not what we’re looking to achieve! Remember that we want to know how many students are from each country in our table. Therefore to accomplish this task, we need to apply the GROUP BY clause as follows:
 
SELECT country, COUNT(*)
FROM Student
GROUP BY country;
 
Thus the query will return the following result:
 
 

+-----------+----------------+
| country   | home_city      |
+-----------+----------------+
| USA       |      2         |
| Belgium   |      1         |
| England   |      1         |
| Indonesia |      1         |
+-----------+----------------+
 
 
MySQL Count Distinct
 
Another important variation of the MySQL Count() function is the application of the DISTINCT clause into what is known as the MySQL Count Distinct. And here’s the syntax for this application:
 
COUNT (DISTINCT expression,[expression...]);
 
So going back to our students table again, we have the following:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT004 | Alicia K.West        | England   | Bangalore      |
| SDT005 | Musapha Ahmed        | Indonesia | Arnsberg       |
+--------+----------------------+-----------+----------------+
 
 
Now, suppose we want to count the unique values in the country column and display them to the screen, we’ll run the following MySQL Count(DISTINCT) query:
 
SELECT COUNT(DISTINCT(country));
 
The above query will return the following values:
 
 

+-----------+-----------------+
| COUNT(DISTINCT(country))    |
+-----------+-----------------+
|           USA               |
|         Belgium             |
|         England             |
|        Indonesia            |
+-----------+-----------------+
 
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]
 
 
MySQL Count Unique Values In Column
 
So as you can observe from the above, the MySQL Count unique values in the column can be accomplished using the MySQL Count(DISTINCT). And this is an easy and simple way to count and determine how the unique values in a table no matter how many times they occur.
 
And this is evident in our student’s table because the country “USA” occurs twice in the table and the function returns only one occurrence of it which is correct and appropriate.
 
– MySQL Count Duplicates
 
Certainly, you may be wondering how to find duplicate values in the MySQL database. Surely there’s a way to see this using the MySQL Count Duplicates which enables you to find duplicates in your records.
 
So let’s explore how this works with an example. Take a look at the students table below:
 
 

+--------+----------------------+-----------+----------------+
| sdt_id | sdt_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| SDT001 | James Baker          | USA       | Cambridge      |
| SDT002 | Jessica Norton       | Belgium   | Toronto        |
| SDT004 | Jessica Norton       | Belgium   | Toronto        |
| SDT003 | Benjamin Smith       | USA       | Leeds          |
| SDT005 | Alicia K.West        | England   | Bangalore      |
| SDT006 | Musapha Ahmed        | Indonesia | Arnsberg       |
| SDT007 | Alicia K.West        | England   | Bangalore      |
| SDT008 | James Baker          | USA       | Cambridge      |
+--------+----------------------+-----------+----------------+
 
 
As you observe from the above students tables, there are some rows with duplicates across the sdt_name, country, and home_city columns. So the question is how do we identify and find them programmatically using SQL?
 
In order to do this, we need to first of all group all rows by a target column (i.e., the column you want to check for duplicates) and then employ the Count() function with the HAVING clause to identify those groups having more than 1 element – this how you perform the MySQL Count Duplicates.
 
Here’s the query that gets the job done:
 
SELECT sdt_name, COUNT(sdt_name)
FROM Student
GROUP BY sdt_name
HAVING COUNT(sdt_name) > 1;
 
And the result will look like:
 
 

+--------+----------------------+-----------+
| sdt_name             | COUNT(sdt_name)    |
+--------+----------------------+-----------+
| James Baker          |         2          |
| Jessica Norton       |         2          |
| Benjamin Smith       |         1          |
| Alicia K.West        |         2          |
| Musapha Ahmed        |         1          |
+--------+----------------------+-----------+
 
 
Tada! And that’s all there is to it!
 
Final Thoughts
 
So we hope you found this guide on the Count() function MySQL database most help. Leave a comment below and let us know other tips and tricks on how you count and aggregate data in MySQL.
 
 
[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
 
[BENEFITS]:
 
 
FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
 
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
 
Multi-Domain Hosting & 99.9% Uptime Guarantee!
 
Super Fast Servers with 24/7/365 Technical Support!
 
 
Click here to access this [SPECIAL OFFER]