Introduction

The Count statement returns the number of items found in a groupCount operates like the COUNT_BIG function. This statement is used for the data types of their return values. Count always returns an int data type value. COUNT_BIG always returns a bigint data type value. The Count() function returns the number of rows in a table satisfying the criteria specified in the where clause. It sets the number of rows or non-NULL column values. Count() returns 0 if there are no matching rows.

Syntax 

SELECT COUNT(column_name)      
FROM table_name      
WHERE condition;    

Using Count and Distinct statement

The Count and distinct statement returns the number of titles a Sample in OrderDetails can hold in SQL. 

SELECT COUNT(DISTINCT OrderName)        
FROM OrderDetails       

This query would return the number of unique orders that have been placed. This information could be used to track the popularity of different products or services or to identify trends in customer behavior.

Output

Note. The Count statement * is used for Count (); all records ( rows ) are Counted if some content is NULL but Count(column_name) does not Count a record if its field is NULL.

Using Select Count statement (*)

This statement returns the total number of Sample cycles EmployeeDetail

SELECT COUNT(*)        
FROM EmployeeDetail;    

This query returns the total number of employee records in the EmployeeDetail table.

Output

Using Count ( ) with column name

In this statement, the SQL Count() function excludes the Null values for a specific column if it specifies the column is an argument in the parenthesis of the Count function. 

SELECT COUNT(OrderId)          
FROM OrderDetails ;   

This query is retrieving the Count of records in the OrderDetails table. Specifically, it counts the number of OrderId values present in the table.

Output

Using Select Count (*) for multiple Tables

This statement count the number of rows from two tables (here, we use EmployeeDetail and OrderDetails) using the Count(*) command. 

SELECT
    (SELECT COUNT(*) FROM EmployeeDetail) AS Total_Employees,
    (SELECT COUNT(*) FROM OrderDetails) AS OrderName
FROM OrderDetails;

In this query, the subqueries (SELECT COUNT(*) FROM EmployeeDetail) and (SELECT COUNT(*) FROM OrderDetails) are used to obtain the Count of records from the respective tables. The main query select these counts along with the data from the OrderDetails table.

Note. The OrderName alias is not meaningful in the query context. It would be more appropriate to use an alias that reflects the Count of orders, such as Total_Orders, for clarity.

Output

Using Select Count(*) with other aggregates

This statement shows that Count(*) works with other aggregate functions in the select list. The example uses the Sample database. 

SELECT COUNT(*), AVG(OrderId)
FROM OrderDetails
WHERE OrderId > 5;  

This query will return the Count of rows where the OrderId is greater than 5, as well as the average value of the OrderId for those rows.

Output

Using the over clause in the statement

This statement uses the ORDER BY and sum functions with the OVER clause to return aggregated values for each department in the Sample database OrderDetails table. 

SELECT OrderName, OrderAddress, SUM(OrderId) OVER (PARTITION BY OrderName ORDER BY OrderAddress) AS OrdrId
FROM OrderDetails;

This query will return the OrderName OrderAddress, and the calculated sum of the OrderId column over each partition defined by the unique values in the OrderName column, ordered by the OrderAddress column. The calculated sum will be displayed as the alias OrderId in the result set.

Output

Application of Count() function

In this statement, The COUNT() function is commonly used in SQL queries to calculate the number of rows that match a specific condition or to count the total number of rows in a table. Here are some common applications of the COUNT() function

Count with Group by page discusses how to apply the Count function with Group By in ascending and descending order. 

SELECT COUNT(*) FROM OrderDetails;

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

SELECT COUNT(*) FROM OrderDetails WHERE OrderId=1;

This query counts the number of rows in the OrderDetails table that satisfy the specified condition. The condition can include one or more column comparisons, logical operators, and functions. 

SELECT COUNT(DISTINCT OrderName) FROM OrderDetails

This query calculates the number of distinct values in the specified column of the OrderName table. It eliminates duplicate values before counting. 

SELECT OrderAddress, COUNT(*) FROM orderDetails  GROUP BY OrderAddress ;

This query groups the rows in the OrderDetails table based on the values in OrderAddress and calculates the Count of rows in each Group.

SELECT COUNT(CASE WHEN Condition THEN 1 ELSE NULL END) FROM TableName;

This query count the rows in the TableName table based on a specific condition using the case statement. It count rows where the condition evaluates to true and ignores rows where it evaluates to false or NULL.

Conclusion

In this article, you will learn about the code that taught us SQL count statement.

FAQs

Q- What is the purpose of the SQL COUNT statement?

A- The SQL COUNT statement is used to count the number of rows that match a specific condition in a table.

Q- How does the SQL COUNT statement work?

A- The SQL COUNT statement works by applying a condition or filter to a table and returning the Count of rows that satisfy that condition.

Q- What does the asterisk () mean in the COUNT statement?

A- The asterisk () is a wildcard character representing all table columns. Using COUNT(*) counts all rows in the specified table.

Q- Can the SQL COUNT statement be used with GROUP BY?

A- Yes, the SQL COUNT statement can be used with the GROUP BY clause to count the number of rows per Group. For example, you can count the number of employees in each department.

Q- Are there any alternative ways to achieve the same result as the SQL COUNT statement?

A- Yes, there are alternative ways to achieve similar results. For example, you can use the SQL SUM statement with a conditional expression to count rows that meet certain criteria.

Q- What is the difference between COUNT() and COUNT(column_name)?

A- COUNT() counts all rows in a table, regardless of the column values. COUNT(column_name) counts the number of non-null values in the specified column.

Q- Can the SQL COUNT statement be used on multiple tables?

A- Yes, the SQL COUNT statement can be used with JOIN operations to count rows that meet conditions across multiple tables.