MySQL COUNT() Function

 The COUNT() function in MySQL is an aggregate function that returns the number of rows that match a specified condition or the total number of rows in a table.

Syntax

There are two primary ways to use the COUNT() function:

1. Counting all rows in a table:

   SELECT COUNT(*) FROM table_name;
   

   • COUNT(*) counts all rows in the table, including rows with NULL values.

2. Counting non-NULL values in a specific column:

   SELECT COUNT(column_name) FROM table_name;
   

   • COUNT(column_name) counts only the rows where the specified column has a non-NULL value.

Examples

1. Count all rows in a table

If you have a table employees and you want to count how many rows are in the table:

SELECT COUNT(*) FROM employees;

This will return the total number of rows in the employees table, including rows with NULL values in any column.

2. Count non-NULL values in a specific column

If you want to count how many employees have a non-NULL value in the email column:

SELECT COUNT(email) FROM employees;

This will return the number of employees who have an email address (i.e., the rows where the email column is not NULL).

3. Count with a WHERE condition

You can also use COUNT() with a WHERE clause to count rows based on a specific condition.

For example, if you want to count how many employees have a salary greater than 50000:

SELECT COUNT(*) FROM employees WHERE salary > 50000;

4. Count distinct values

You can also use COUNT(DISTINCT column_name) to count the number of unique non-NULL values in a column.

For example, to count how many distinct departments exist in the employees table:

SELECT COUNT(DISTINCT department) FROM employees;

Notes

• COUNT(*) counts rows even if some columns contain NULL values.
• COUNT(column_name) counts only rows where the specified column is not NULL.
• COUNT(DISTINCT column_name) counts unique non-NULL values of the specified column.

Summary

• COUNT(*): Counts all rows in the table (including those with NULL values).
• COUNT(column_name): Counts rows where the specified column has a non-NULL value.
• COUNT(DISTINCT column_name): Counts distinct non-NULL values in the specified column.

Let me know if you'd like any further clarification or examples!