SQL Server COUNT()

SQL Server COUNT() Function

The SQL Server COUNT() function returns the number of rows that match a specified condition. This function is useful for counting rows in a table or the results of a query.

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

The COUNT() function can be used in several ways:

  • COUNT(column_name): Counts the number of non-NULL values in a specified column.
  • COUNT(*): Counts the total number of rows in a table, including rows with NULL values.
  • COUNT(DISTINCT column_name): Counts the number of distinct non-NULL values in a specified column.

Example SQL Server COUNT() Function Queries

Let's look at some examples of SQL Server COUNT() function queries:

1. Basic COUNT() Example

SELECT COUNT(*) AS total_rows
FROM employees;

This query counts the total number of rows in the employees table. The result will be:

total_rows
-----------
10

2. COUNT() with a Condition

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

This query counts the number of rows in the employees table where the department is 'Sales'. The result will be:

sales_employees
--------------
4

3. COUNT() with a Column

SELECT COUNT(salary) AS non_null_salaries
FROM employees;

This query counts the number of non-NULL values in the salary column of the employees table. The result will be:

non_null_salaries
-----------------
8

4. COUNT() with DISTINCT

SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;

This query counts the number of distinct values in the department column of the employees table. The result will be:

unique_departments
------------------
5

5. COUNT() with a Variable

DECLARE @dept VARCHAR(50);
SET @dept = 'Engineering';
SELECT COUNT(*) AS engineering_employees
FROM employees
WHERE department = @dept;

This query uses a variable to specify the department and then counts the number of employees in that department. The result will be:

engineering_employees
---------------------
2

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the COUNT() function.

Step 1: Creating a Table

This step involves creating a new table named employees to store some sample data.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, name, department, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, name, department, salary) VALUES (1, 'John Doe', 'Engineering', 75000);
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Jane Smith', 'Sales', 60000);
INSERT INTO employees (id, name, department, salary) VALUES (3, 'Jim Brown', 'Engineering', 65000);
INSERT INTO employees (id, name, department, salary) VALUES (4, 'Jake White', 'Sales', 50000);
INSERT INTO employees (id, name, department, salary) VALUES (5, 'Jill Green', 'HR', 55000);
INSERT INTO employees (id, name, department, salary) VALUES (6, 'James Black', 'Marketing', 45000);
INSERT INTO employees (id, name, department, salary) VALUES (7, 'Jenny Blue', 'Sales', 62000);
INSERT INTO employees (id, name, department, salary) VALUES (8, 'Jack Gray', 'IT', NULL);
INSERT INTO employees (id, name, department, salary) VALUES (9, 'Joy Brown', 'Engineering', NULL);
INSERT INTO employees (id, name, department, salary) VALUES (10, 'Jerry Pink', 'Sales', 58000);

Here, we insert data into the employees table.

Step 3: Using the COUNT() Function

This step involves using the COUNT() function to count the number of rows in the employees table.

SELECT COUNT(*) AS total_rows
FROM employees;

This query retrieves the total number of rows in the employees table. The result will be:

total_rows
-----------
10

Conclusion

The SQL Server COUNT() function is a powerful tool for counting the number of rows that match a specified condition. Understanding how to use the COUNT() function and its syntax is essential for effective data analysis and processing in SQL Server.