You are currently viewing SQL 1.16 SQL COUNT()

SQL 1.16 SQL COUNT()

Introduction to SQL COUNT() Function

SQL COUNT() is a powerful aggregate function used to count the number of rows returned by a query. It is commonly used to calculate the number of records in a table that meet certain criteria. This tutorial will provide you with a detailed understanding of the SQL COUNT() function and its usage in various scenarios.

Prerequisites:
Before diving into this tutorial, it is recommended to have a basic understanding of SQL syntax and database concepts.

Syntax of SQL COUNT() Function


The syntax for SQL COUNT() function is as follows:

SELECT COUNT(column_name) FROM table_name WHERE condition;
  • column_name: The column or expression to count. It can be a specific column name, a wildcard (*), or an expression.
  • table_name: The name of the table from which to retrieve data.
  • condition: (Optional) The condition that must be met for the rows to be counted. It filters the rows based on the specified condition.

Basic Usage of SQL COUNT()


Let’s start with a simple example to understand the basic usage of SQL COUNT() function.

Consider a table named employees with the following schema:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
);

Sample data in the employees table:

| id |   name   |  department  |
|----|----------|--------------|
| 1  | John     | Engineering   |
| 2  | Alice    | Sales        |
| 3  | Bob      | Engineering  |
| 4  | Mary     | HR           |

Now, let’s count the total number of employees in the employees table:

SELECT COUNT(*) AS total_employees FROM employees;

Explanation:

  • COUNT(*) counts all rows in the table.
  • AS total_employees aliases the result column as total_employees.

Output:

| total_employees |
|-----------------|
|        4        |

Counting Specific Rows


You can use SQL COUNT() with conditions to count specific rows based on certain criteria.

For example, let’s count the number of employees in the Engineering department:

SELECT COUNT(*) AS engineering_employees FROM employees WHERE department = 'Engineering';

Explanation:

  • WHERE department = 'Engineering' filters the rows to include only those where the department is Engineering.

Output:

| engineering_employees |
|-----------------------|
|           2           |

Handling NULL Values


SQL COUNT() function excludes NULL values by default. However, you can include NULL values in the count by using the COUNT(column_name) function.

Consider a table named products with the following schema:

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    category VARCHAR(50)
);

Sample data in the products table:

| id |   name     |  category   |
|----|------------|-------------|
| 1  | Laptop     | Electronics |
| 2  | Printer    | Electronics |
| 3  | Smartphone | Electronics |
| 4  | NULL       | Electronics |

Now, let’s count the total number of products (including NULL values) in the products table:

SELECT COUNT(name) AS total_products FROM products;

Explanation:

  • COUNT(name) counts the non-NULL values in the name column.

Output:

| total_products |
|----------------|
|        3       |

Conclusion


In this tutorial, you learned the basics of SQL COUNT() function and its usage in different scenarios. You can now count the number of rows in a table, count specific rows based on conditions, and handle NULL values effectively using the SQL COUNT() function. Experiment with different queries and scenarios to solidify your understanding. Happy querying!

Leave a Reply