The COUNT() function in MySQL is an aggregate function used to count the number of rows that match a specified condition or the number of non-NULL values in a column. It is widely used in SQL queries to summarize data and perform analysis.

Syntax

1. Count All Rows

COUNT(*)

• Counts the total number of rows in a table, including rows with NULL values.

2. Count Non-NULL Values in a Column

COUNT(column_name)

• Counts the number of non-NULL values in a specific column.

3. Count Unique Non-NULL Values

COUNT(DISTINCT column_name)

• Counts the number of unique non-NULL values in a specific column.

Examples

1. Count All Rows

   To count the total number of rows in a table:

   SELECT COUNT(*) AS total_rows
   FROM employees;
   

   This query returns the total number of rows in the employees table.

2. Count Non-NULL Values in a Column

   To count the number of non-NULL values in a specific column:

   SELECT COUNT(salary) AS total_salaries
   FROM employees;
   

   This query counts the number of non-NULL values in the salary column of the employees table.

3. Count Unique Values in a Column

   To count the number of unique non-NULL values in a column:

   SELECT COUNT(DISTINCT department) AS unique_departments
   FROM employees;
   

   This query counts the number of unique departments in the employees table.

4. Count Rows with a Condition

   To count the number of rows that meet a specific condition:

   SELECT COUNT(*) AS active_employees
   FROM employees
   WHERE status = 'Active';
   

   This query counts the number of rows in the employees table where the status column is 'Active'.

5. Count Rows by Group

   To count the number of rows for each group when using the GROUP BY clause:

   SELECT department, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department;
   

   This query retrieves the number of employees in each department.

6. Count with Multiple Conditions

   To count rows that satisfy multiple conditions:

   SELECT COUNT(*) AS senior_employees
   FROM employees
   WHERE position = 'Senior' AND salary > 50000;
   

   This query counts the number of senior employees with a salary greater than 50,000.

Best Practices

• Use with GROUP BY: COUNT() is often used with the GROUP BY clause to provide counts for each group of data.
• Include Non-Aggregated Columns: When using GROUP BY, ensure that all non-aggregated columns in the SELECT clause are included in the GROUP BY clause.
• Filter Before Counting: Use the WHERE clause to filter data before applying COUNT() to ensure accurate results based on specific criteria.
• Avoid Unnecessary Counts: Be mindful of using COUNT(*) with large tables as it can be resource-intensive. Consider filtering or optimizing queries for better performance.