SQL COUNT Statement

Introduction 

 
In this chapter, we learned how to use a SQL COUNT  statement with various options. 
 

SQL COUNT Statement

 
This statement is used to return the number of items found in a group Count operates like the COUNT_BIG function. These statements differ only in 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 were no matching rows.
 
Syntax 
  1. SELECT COUNT(column_name)      
  2. FROM table_name      
  3. WHERE condition;    
This example is the general SQL 2019 ANSI standard syntax. It helps us understand the way in which the SQL Count() Function is used. Different database vendors may have different ways of applying the Count() function.
 
Below, you can see that MySQL, PostgreSQL, and Microsoft SQL Server follow the same syntax as given above, but DB2 and oracle differ slightly.
 
Overall, you can use * or ALL or distinct or some expression along with the count, in order to count the number of rows for some condition or all of the rows, depending upon the arguments you are using along with the count() function.
 

Using Count and Distinct statement

 
This example returns the number of different titles that a Sample in OrderDetails can hold.
 
Syntax 
  1. SELECT COUNT(DISTINCT OrderName )        
  2. FROM OrderDetails       
  3. GO     
Example 
 
SQL COUNT Statement
 
Note
 
The Count statement * is used for Count(), all records ( rows ) are Counted if some content NULL but Count(column_name) does not Count a record if its field is NULL
 

Select Count statement (*)

 
This example returns the total number of Sample cycles EmployeeDetail
 
Syntax
  1. SELECT COUNT(*)        
  2. FROM EmployeeDetail;        
  3. GO    
Example
 
SQL COUNT Statement
 

Using Count( ) with column name

 
In this example, SQL Count() function excludes the NULL values for a specific column if specified the column as an argument in the parenthesis of the Count function. 
 
Syntax
  1. SELECT COUNT(OrderId)          
  2. FROM OrderDetails ;   
Example
 
SQL COUNT Statement
 

Select Count (*) for multiple Tables 

 
The following query counts the number of rows from two different tables (here we use EmployeeDetail and OrderDetails) using the Count(*) command. 
 
Syntax 
  1. SELECT(    
  2.       SELECT COUNT(*)    
  3.       FROM  EmployeeDetail    
  4.       ) AS Total_Employees,    
  5.       (SELECT COUNT(*)    
  6.       FROM  OrderDetails    
  7.       ) AS OrderName     
  8. FROM OrderDetails     
Example
 
SQL COUNT Statement
 

Select Count(*) with other aggregates

 
This example shows that Count(*) works with other aggregate functions in the Select list. The example uses the Sample database. 
 
Syntax
  1. SELECT COUNT(*),AVG(OrderId)  
  2.  FROM OrderDetails    
  3. Where OrderId>5;    
  4. GO      
Example
 
SQL COUNT Statement
 

Using the over a clause in the statement

 
This example uses the Min, Max, Avg, and Count functions with the Over clause, to return aggregated values for each department in the Sample database OrderDetails table.
 
Syntax
  1. SELECT DISTINCT OrderName      
  2.        , MIN(OrderId) OVER (PARTITION BY edh.OrderId) AS OrderDetails        
  3.        , MAX(EmpId) OVER (PARTITION BY edh.OrderId) AS EmployeeDetail       
  4.        , AVG( EmployeeID) OVER (PARTITION B edh.OrderId)   class="string">"keyword">AS EmployeeDetails       
  5.        ,COUNT(OrderId) OVER (PARTITION BY edh.OrderId) class="keyword">AS OrderDetails        
  6. FROM EmployeeDetail AS eph        
  7. JOIN EmployeeDetails AS edh        
  8.      ON eph.EmpId = edh.EmployeeID        
  9. JOIN OrderDetails  AS d        
  10. ON d.OrderId = edh.EmployeeID      
  11. WHERE edh.EmployeeID IS   
  12. ORDER BY EmployeeName;    

Application of Count() function

 
In this section, we have discussed how to apply to count() withvarious SQL clauses for those applications, we have used Oracle 10g express edition.
 
Count with Distinct page discusses how to apply the count function with distinct and also discusses how to apply the count function with All clause. unlike using *,when all is used, Null values are not selected.
 
Count Having page discusses how to apply the count function with the Having clause and group by.
 
Count with Group by page discusses how to apply the Count function with Group By in ascending order and in descending order.
 

Summary

 
In this chapter, we learned how to use a SQL DATE TIME statement with various options.
Author
Naresh Beniwal
Editor 7.1k 1.6m
Next » SQL Aggregate Statement

©2024 C# Corner.