TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

MySQL count()

MySQL count() function example with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

MySQL Count() Function

MySQL count() function is used to returns the count of an expression. It allows us to count all rows or only some rows of the table that matches a specified condition. It is a type of aggregate function whose return type is BIGINT. This function returns 0 if it does not find any matching rows.

We can use the count function in three forms, which are explained below:

  • Count (*)
  • Count (expression)
  • Count (distinct)

Let us discuss each in detail.

COUNT(*) Function: This function uses the SELECT statement to returns the count of rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.

COUNT(expression) Function: This function returns the result set without containing Null rows as the result of an expression.

COUNT(distinct expression) Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.

Syntax

The following are the syntax of the COUNT() function:

SELECT COUNT (aggregate_expression)  
FROM table_name  
[WHERE conditions];  

Parameter explanation

aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted.

table_name: It specifies the tables from where you want to retrieve records. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.

MySQL count() function example

Consider a table named "employees" that contains the following data.

mysql count()

Let us understand how count() functions work in MySQL.

Example1

Execute the following query that uses the COUNT(expression) function to calculates the total number of employees name available in the table:

mysql> SELECT COUNT(emp_name) FROM employees;  

Output:

mysql count()

Example2

Execute the following statement that returns all rows from the employee table and WHERE clause specifies the rows whose value in the column emp_age is greater than 32:

mysql> SELECT COUNT(*) FROM employees WHERE emp_age>32;  

Output:

mysql count()

Example3

This statement uses the COUNT(distinct expression) function that counts the Non-Null and distinct rows in the column emp_age:

mysql> SELECT COUNT(DISTINCT emp_age) FROM employees;

Output:

mysql count()

MySQL Count() Function with GROUP BY Clause

We can also use the count() function with the GROUP BY clause that returns the count of the element in each group. For example, the following statement returns the number of employee in each city:

mysql> SELECT emp_name, city, COUNT(*) FROM employees GROUP BY city;

After the successful execution, we will get the result as below:

mysql count()

MySQL Count() Function with HAVING and ORDER BY Clause

Let us see another clause that uses ORDER BY and Having clause with the count() function. Execute the following statement that gives the employee name who has at least two age same and sorts them based on the count result:

mysql> SELECT emp_name, emp_age, COUNT(*) FROM employees 
GROUP BY emp_age 
HAVING COUNT(*)>=2 
ORDER BY COUNT(*);

This statement will give the output as below:

mysql count()
Next TopicMySQL sum()




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf