COUNT SQL: how to count records in SQL

The COUNT command allows you to count the number of records that satisfy a specific condition in a search. COUNT SQL is widely used in various contexts. From sales and stock analyzing to the evaluation of systems and processes.

In this article, we will explore COUNT, addressing its forms of use. Also, characteristics and applications in different sectors and contexts. 

Mostly any database supports COUNT in SQL. For example, in the Copahost website hosting MySQL database.

When is COUNT SQL utilized?

Various situations and contexts uses COUNT in SQL. Its main purpose is to count the number of records that meet a certain condition or criteria. Let’s list some useful examples of using COUNT:

1. Record count: we should use to determine the number of records in a table. Without importing the selected columns. For example, we can apply COUNT(*) to count all records in a sales table.
2. Counting records with certain characteristics: We can combine COUNT with other SQL statements, such as UPDATE , JOIN and WHERE, to count only records that meet a criteria. For example, we can use COUNT(*) with WHERE to count only records with a specific value in a column.
3. Count of unique values: We can also use COUNT to count the number of unique values ​​in a column of a table. For example, we use COUNT(DISTINCT column) to count the number of unique values ​​in a column of a table.
4. Data analysis: We use COUNT extensively in data analyzing to identify patterns, trends and problems in systems and processes. For example, to analyze the frequency of problems in a system. Or even to identify the best-selling items in a store.
5. Stock and stock tracking : We use COUNT SQL to control the number of items in stock or inventory. For example, we use COUNT to count the number of items in stock at a given time.

COUNT variants

It COUNT SQLhas several variants that we can use to obtain different results and analyze data more precisely. Let’s explore the main variants of COUNT SQL:

1. COUNT()– The most basic form of COUNT . We use COUNT() to count all records in a table, without importing the selected columns. For example, we can count the total number of records in the table  employees. See below:

SELECT COUNT(*) FROM employees;

2. COUNT(column)– We know that we use the variant COUNTto count the number of records with a specific value in a table column. In this sense, COUNT(column) only takes into account records for which the specified column is not null. For example, we can count the number of records in the table  employees that have a salary value greater than 50000. See below:

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

3. COUNT(expression)– We use it COUNT(expression) to count the number of records that satisfy a specific condition. Thus, the expression can include multiple clauses, such as WHERE, AND, OR, and NOT. For example, we can count the number of records in the table  employees that have a sales department and a salary greater than 40000.

SELECT COUNT(*) FROM employees WHERE department = 'Sales' AND salary > 40000;

4. COUNT(DISTINCT column)– We apply the variant COUNTto count the number of unique values ​​in a column of a table. This way, COUNT(DISTINCT column)only takes into account unique values ​​in a column, excluding duplicate values. For example, we can count the number of unique values ​​in the  employee_id table  column sales. See below:

SELECT COUNT(DISTINCT employee_id) FROM sales;

Data aggregation using COUNT SQL

Data aggregations are complex mathematical operations that combine multiple numeric values ​​to produce a single aggregate value. They are common in databases and are used to summarize and analyze complex information in a table. Thus, these operations can include sum, average, count, and other mathematical functions that help you analyze and better understand the data in a table. Data aggregations are an important part of data manipulation and are used to extract valuable insights and useful information from data stored in a database.

How is COUNT SQL related to data aggregations?

COUNT SQL is related to data aggregations because it is an aggregation function that combines numeric values ​​to produce a single aggregate value.

An SQL query’s table can contain multiple columns and rows, and data aggregations allow you to summarize and analyze that data in an easier-to-understand way. COUNT SQL is one of the most common aggregate functions in SQL and is often used to count the number of rows in a table or subquery.

For example, suppose we have a sales table that contains information about the products sold, the date of sale, and the total value of the sale. This way, to get an overview of sales. We can use the COUNT function to count the total number of sales in the table:

SELECT COUNT(*) FROM sales;

So, this will return a single value, which we can use to calculate other sales metrics, such as the number of sales per month or per product category.

Furthermore, COUNT can also be applied to count the number of rows in a subquery. Thus, this is useful when we want to get detailed information about a subset of data in a table. For example:

SELECT COUNT(*) FROM (SELECT * FROM sales WHERE date >= '2022-01-01' AND date <= '2022-02-28');

This will return the number of sales made in the month of January 2022.

Usage examples in data aggregations

There are many examples of how COUNT SQL can be applied to data aggregations, here are some examples:

1. Total record count:

SELECT COUNT(*) FROM table;

This command returns the total number of records in the table “table”.

2. Record count by category:

SELECT category , COUNT(*) as count FROM table GROUP BY category ;

Thus, the command returns the number of records in each category in the “table” table.

3. Record count per month:

SELECT MONTH(date) as mes, COUNT(*) as count FROM table GROUP BY MONTH(date);

This way, the command returns the number of records in each month in the “date” column of the “table” table.

4. Record count by city and category:

SELECT city , category, COUNT(*) as count FROM table GROUP BY city, category ;

This command returns the number of records in each city and category in the “table” table.

5. Record count by city and month:

SELECT city, MONTH(date) as month, COUNT(*) as count FROM table GROUP BY city, MONTH(date);

This command returns the number of records in each city and month in the “date” column of the “table” table.

Examples using COUNT in conjunction with other functions

Here are some examples of how to use the COUNT function in conjunction with other functions in SQL:

1. Count the number of sales by state

Suppose we have a table called “sales” that contains information about each sale made by the company, including the sale ID, customer status, sale amount, and number of items purchased. So, To count the number of sales for each state. We can use the COUNT function in conjunction with the GROUP BY function and the COUNT function again. Here is an example of how to do this:

SELECT country, COUNT(*) as total_sales
FROM sales
GROUP BY country;

2. Count the number of sales by product category

Suppose we have a table called “sales” that contains information about each sale made by the company, including the sale ID, product category, sale value, and number of items purchased. This way, to count the number of sales for each product category, we can use the COUNT function in conjunction with the GROUP BY function and the COUNT function again. Here is an example of how to do this:

SELECT product_category, COUNT(*) as total_sales
FROM sales
GROUP BY product_category;

3. Count the number of sales per time period

Suppose we have a table called “sales” that contains information about each sale made by the company, including the sale ID, date of sale, sales amount, and number of items purchased. Therefore, to count the number of sales for each time period, we can use the COUNT function in conjunction with the DATEDIFF function and the WHERE function. Here is an example of how to do this:

SELECT DATEDIFF(day, '2022-01-01', sale.date_venda) as period, COUNT(*) as total_sales
FROM sales
GROUP BY DATEDIFF(day, '2022-01-01', sale.date_sale);

4. Finding the company’s oldest customer

Suppose we have a table called “customers” that contains information about each company’s customer, including the customer ID, customer name, and customer age. In this sense, to find the company’s oldest customer. We can use the COUNT function in conjunction with the JOIN function and the WHERE function. In addition, we can also apply the CASE WHEN and DELETE function in conjunction with COUNT.Here is an example of how to do this:

SELECT c.name as client, c.age as age
FROM client c
JOIN (SELECT id, MAX(age) as older_age
      FROM client
      GROUP BY id) m
ON c.id = m.id AND c.age= m.older_age

5. Finding the customer who made the highest value sale

Suppose we have a table called “sales” that contains information about each sale made by the company, including the sale ID, customer name, sale amount, and date of sale. Thus, to find the customer who made the sale with the highest value. We can use the COUNT function in conjunction with the JOIN function and the WHERE function. In that sense, here is an example of how to do this:

SELECT c.name as client, v.value as value_sales
FROM sales v
JOIN client c ON v.client_id = c.id
WHERE v.value = (SELECT MAX(value) FROM sales WHERE client_id = v.client_id)