How the COUNT() Function Behaves with Different Parameters (Basic 1 - 14)

The COUNT() function in SQL is an aggregate function that returns the number of rows in a table or group of rows. It can be used with different parameters to count different things, such as the total number of rows, the number of rows with a specific value in a column, or the number of distinct values in a column.

Counting the total number of rows

To count the total number of rows in a table, use the following syntax:

SQL

SELECT COUNT(*) FROM table_name;

The COUNT(*) function will count all of the rows in the table, regardless of whether they contain NULL values or duplicates.

Example:

SQL

SELECT COUNT(*) FROM employees;

This query will return the total number of rows in the employees table.

Counting the number of rows with a specific value in a column

To count the number of rows with a specific value in a column, use the following syntax:

SQL

SELECT COUNT(column_name) FROM table_name WHERE column_name = value;

The COUNT(column_name) function will count the number of rows in the table where the column_name column is equal to the value parameter.

Example:

SQL

SELECT COUNT(department) FROM employees WHERE department = 'Sales';

This query will return the number of rows in the employees table where the department column is equal to Sales.

Counting the number of distinct values in a column

To count the number of distinct values in a column, use the following syntax:

SQL

SELECT COUNT(DISTINCT column_name) FROM table_name;

The COUNT(DISTINCT column_name) function will count the number of unique values in the column_name column.

Example:

SQL

SELECT COUNT(DISTINCT department) FROM employees;

This query will return the number of distinct departments in the employees table.

Counting NULL values and non-NULL values

The COUNT() function can also be used to count the number of NULL values and non-NULL values in a column. To do this, you can use the following syntax:

SQL

SELECT COUNT(column_name) AS non_null_count, COUNT(*) - COUNT(column_name) AS null_count FROM table_name;

This query will return the number of non-NULL values and the number of NULL values in the column_name column.

Example:

SQL

SELECT COUNT(salary) AS non_null_count, COUNT(*) - COUNT(salary) AS null_count FROM employees;

This query will return the number of employees with a salary and the number of employees without a salary.

Conclusion

The COUNT() function is a powerful tool that can be used to count rows and values in a variety of ways. By understanding the different parameters that can be used with the COUNT() function, you can use it to get the information you need about your data.