The SQL Server COUNT Function is one of the Aggregate Functions that counts and returns the total number of rows (records) selected by the SELECT Statement in a table. By default, the COUNT function uses the ALL keyword. So, it counts all the rows available in a given table that includes the NULL values and duplicate rows. Microsoft SQL Server provides various combinations of the COUNT function to deal with NULLs, duplicates (finding the distinct values), conditional counting, etc.
For example, when you need to get the total number of products in your store or sales evaluation? Or you want to find the orders placed for Black color (or any other) products from your store. You can use this SQL COUNT function in these situations or for data analytics, reporting, and business analysis.
This article covers detailed information about the SQL Server COUNT() function that includes syntax, its uses, different combinations, condition based counting, and the combination of GROUP BY, HAVING, CASE statement, and window functions.
SQL COUNT Function Syntax
Before showing the basic and complex examples of the SQL Server COUNT Function, let me introduce the simple syntax of it. The code below is the simple version of the COUNT function, and you can add DISTINCT, GROUP BY, HAVING, CASE, etc to them to make it more complex.
SELECT COUNT([Column_Name] | * )
FROM [Source]
WHERE <Search_Condition>
By default, it accepts either * or column name as the argument and counts the total records in that column or table. The following practical examples show you some of the use cases of the COUNT function for better understanding. For this SQL COUNT() function demonstration, we are going to use the below-shown table data and AdventureWorksDW2019.
SQL Server COUNT (*) Example
The COUNT(*) returns the total number of records from the employee’s table. It includes both the NULL values and the duplicates present in the table. Let us see an example to understand this Aggregate Function better.
SELECT COUNT(*) AS [Total Records]
FROM [Employees]
The above Select Statement query finds the total records present in the Employee table.
15In real-time, support you want to find the total sales or total products sold via the internet and the retailer stores. So that, you can check the sales report and use these metrics to do further calculations for business analysis. In this scenario, you can use the below two queries to get the total numbers.
SELECT COUNT(*) AS TotalInternetSales
FROM FactInternetSales;
SELECT COUNT(*) AS TotalRetailerSales
FROM FactResellerSales;
60398
60855NOTE: The COUNT(*) adds up NULL values and Duplicate records, as well. And the (Column Name) considers and adds the Duplicate records.
SQL COUNT (Column Name) Example
When you use either Column name instead of the * as an argument, the COUNT function will return the total number of employees present in the table whose values are NOT NULL (and ignore the NULL Records). Let us see one example by using multiple columns as the argument values.
SELECT COUNT([FirstName]) AS [Total Records]
FROM [Employees]
15The below query counts the total records in the [DeptID] whose values are NOT NULL.
SELECT COUNT(DeptID) AS [Total DepartmentIds]
FROM [Employees];
11In the first statement, we are counting the total number of first names in an employee table. Here, the COUNT function doesn’t consider the duplicates as the problem and there are no NULLs, so it prints the same result as the COUNT(*) query. However, the DeptID column has four NULL records, so the result is 11.
The following query will count the total product and the products with proper descriptions in the DimProducts table.
SELECT COUNT([ProductKey]) AS TotalProducts,
COUNT(EnglishDescription) AS ProductWithDescription
FROM DimProduct;
606 | 396Using Aliases Names
It is always advisable to use the Alias column names for the SQL COUNT() function results. If you ignore the meaning full name, you can’t identify which result belongs to which column. For instance, if you observe the below query output, you can’t identify the total rows of each column. However, if you use the alias names, it will be an easy task.
SELECT COUNT(Education), COUNT(FirstName),
COUNT(DeptID) AS [Total Ids]
FROM [Employees]
Please replace the above query with the below with Alias column names.
SELECT COUNT(Education) AS [Total Education],
COUNT(FirstName) AS [Total FirstName],
COUNT(DeptID) AS [Total IDs]
FROM [Employees]
If you need the AdventureWorks example, use the below query on the FactInternetSales table.
SELECT COUNT(Product_id) AS TotalProducts, COUNT(Sales) AS TotalSales FROM FactInternetSales;
SQL COUNT DISTINCT Values Example
In the above COUNT(Column_Name) example, the query ignores the NULLs but counts the duplicates. In SQL Server, there is a DISTINCT keyword, and if you explicitly specify this keyword, the query will return the unique number of rows present in the table whose values are NOT NULL (Ignores the NULL Records).
TIP: The DISTINCT Keyword is used to remove the Duplicates from the specified column Name.
To demonstrate the result, we have used both the regular COUNT and the DISTINCT keyword within the same query to compare the results. Here, [Total Records] returns the total Department IDs that are not null. [Distinct Records] returns the total unique number of non-duplicate records (by removing duplicates) present in the [DeptID] whose values are NOT NULL.
SELECT COUNT(DeptID) AS [Total Records], COUNT(DISTINCT DeptID) AS [Distinct Records] FROM [Employees]
This example shows the difference between the Normal and the select Distinct count. To better understand, we selected multiple columns with normal and Distinct separately.
SELECT COUNT(Education) AS [Total Education],
COUNT(DISTINCT Education) AS [Distinct Education],
COUNT(FirstName) AS [Total FirstName],
COUNT(DISTINCT FirstName) AS [Distinct FirstName],
COUNT(DeptID) AS [Total Ids],
COUNT(DISTINCT DeptID) AS [Distinct Ids]
FROM [Employees]
In order to count the unique products sold via the internet and retail stores, you can use the below queries.
SELECT COUNT(DISTINCT ProductKey) AS UniqueInternetProducts
FROM FactInternetSales;
SELECT COUNT(DISTINCT ProductKey) AS UniqueResellerProducts
FROM FactResellerSales;
COUNT ALL Example
By default, the SQL Server COUNT function uses the ALL keyword, so whether you mentioned it explicitly or not, it doesn’t matter because it returns the same result as the regular Column_Name. The following Server example shows the same.
SELECT COUNT(ALL Education) AS [Total Education],
COUNT(DISTINCT Education) AS [Distinct Education],
COUNT(ALL FirstName) AS [Total FirstName],
COUNT(DISTINCT FirstName) AS [Distinct FirstName],
COUNT(ALL DeptID) AS [Total Ids],
COUNT(DISTINCT DeptID) AS [Distinct Ids]
FROM [Employees]
SQL COUNT WHERE Clause Example
From the above-mentioned syntax, you may see that the SQL Server also allows you to use the WHERE along with the COUNT function. If you use the WHERE clause, you can filter the records selected by the SELECT statement. So that you can count a few of the table records as per your need.
In this example, we are using the Where Clause to add up and show the total employees whose yearly income is greater than or equal to 60000. Similarly, the other query will count the total employees whose sales are above (greater than) 100.
SELECT COUNT(*) AS [IncomeAbove60000]
FROM [Employees]
WHERE YearlyIncome >= 60000
SELECT COUNT(*) AS [SalesAbove100]
FROM [Employees]
WHERE Sales > 100
COUNT IS NOT NULL Example
In this example, we used IS NOT NULL within the WHERE condition. By default, the COUNT(*) returns all the records but when we use the specific condition like below, it will calculate the total customers whose Department ID is not Null.
SELECT COUNT(*) AS [Total Records]
FROM [Employees]
WHERE DeptID IS NOT NULL
COUNT IS NULL Example
Similar to the above, if we use the SQL Server IS NULL, the function will ignore the regular non-null values and COUNT the total number of NULL records in a table.
SELECT COUNT(*) AS [Total Records]
FROM [Employees]
WHERE DeptID IS NULL
The above examples give an idea of using the WHERE clause. However, the below practical examples help you understand the real-time advantages. For instance, the below query will count the total number of internet sales that happened in the Year 2011.
SELECT COUNT(*) AS SalesIn2013
FROM FactInternetSales
WHERE YEAR([OrderDate]) = 2013;
The below query will find the total orders that happened between the months of January and December of 2012. You can replace the dates by Month or Quarter as per the requirement.
SELECT COUNT(*) AS total_orders
FROM FactInternetSales
WHERE OrderDate BETWEEN '2012-01-01' AND '2012-12-31';
SQL Server COUNT GROUP BY Clause Example
In any aggregate function, the GROUP BY clause plays a vital role because it groups similar items. Next, the COUNT() function performs counting per group instead of the whole table.
In most cases, we usually find the number of customers who belong to a particular region, country, city, etc. To handle these situations, we use the GROUP BY clause to group the customers by region, profession, education, state, etc. Next, we use this COUNT function to find the number of customers present in that group. For instance, the below two queries will find the customers belong to category and city.
To find the total orders by category:
SELECT ProductCategory , COUNT(*) as TotalOrders
FROM Orders GROUP BY ProductCategory;
Similarly, the below query will find the total customers by city and the product category.
SELECT City, ProductCategory, COUNT(*) as TotalCustomers
FROM Sales
GROUP BY city, ProductCategory;
Instead of relying on the random example, let us see the real example using the employee table. The below query groups the employees using the Education column. Next, it finds the total number of Employees belonging to each education group.
SELECT Education, COUNT(*) AS [Total Records]
FROM [Employees]
GROUP BY Education
It is another example to showcase the GROUP BY clause and the SQL COUNT() Function. In this example, we use this along with the regular (including duplicates) and DISTINCT records belonging to each education group by applying the functions on multiple columns and the group by clause.
SELECT Education, COUNT(FirstName) AS [Total FirstName],
COUNT(DISTINCT FirstName) AS [Distinct Name],
COUNT(EmpID) AS [Total Ids],
COUNT(DeptID) AS [Total DeptIds]
FROM [Employees]
GROUP BY Education
The below query will count the total sales by product category by joining the FactInternetSales, DImProduct, DimProductSubcategory, and DimProductCategory tables in the AdventureWorksDW database.
SELECT cat.EnglishProductCategoryName AS Category, COUNT(*) AS SalesCount
FROM FactInternetSales fact
JOIN DimProduct prod ON fact.ProductKey = prod.ProductKey
JOIN DimProductSubcategory sub
ON prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
JOIN DimProductCategory cat ON sub.ProductCategoryKey = cat.ProductCategoryKey
GROUP BY cat.EnglishProductCategoryName;
SQL COUNT WITH GROUP BY HAVING Clause
If you want to check the conditions against the aggregated data, then you have to use the HAVING Clause along with Group By Statement. In general, we may require customers whose orders are more than a certain number or postal code with a threshold number of orders. In these situations, we have to group the sale by postcode or city and then use the HAVING clause to apply the filter on top of the customer GROUP. For example, the following query will group the Customers by their Education and count the records present in each group where EmpID is greater than 1 (filter the group).
SELECT Education, COUNT(FirstName) AS [Total FirstName],
COUNT(DISTINCT FirstName) AS [Distinct Name],
COUNT(EmpID) AS [Total Ids],
COUNT(DeptID) AS [Total DeptIds]
FROM [Employees]
GROUP BY Education
HAVING COUNT(EmpID) > 1
The last line of code will check whether the total number of Employee IDs is greater than one or not. If this is True, then corresponding records will display.
The following query is the same as the GROUP BY example. However, we restrict the records by applying the filter condition of (HAVING COUNT(*) > 10000). So, it will find the product categories with more than 10000 sales.
SELECT cat.EnglishProductCategoryName AS Category, COUNT(*) AS SalesCount
FROM FactInternetSales fact
JOIN DimProduct prod ON fact.ProductKey = prod.ProductKey
JOIN DimProductSubcategory sub ON prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
JOIN DimProductCategory cat ON sub.ProductCategoryKey = cat.ProductCategoryKey
GROUP BY cat.EnglishProductCategoryName
HAVING COUNT(*) > 10000;
SQL Server COUNT function ORDER BY Clause
You can use this COUNT function in the ORDER BY Clause to sort the values in ascending or descending. The below query prints the total number of records in an employee table group by Education. Next, Order By Clause will sort those query results based on the Distinct records of a first name result in descending order.
SELECT Education, COUNT(FirstName) AS [Total FirstName],
COUNT(DISTINCT FirstName) AS [Distinct Name],
COUNT(DeptID) AS [Total DeptIds]
FROM [Employees]
GROUP BY Education
ORDER BY COUNT(DISTINCT FirstName) DESC
The below code will join DimGeography and FactInternetSales to count total sales for each country and order by the country name.
SELECT Geo.EnglishCountryRegionName AS Country,
COUNT(*) AS SalesCount
FROM FactInternetSales AS Fact
INNER JOIN DimGeography AS Geo
ON Geo.[SalesTerritoryKey] = Fact.[SalesTerritoryKey]
GROUP BY Geo.EnglishCountryRegionName
ORDER BY Geo.EnglishCountryRegionName
COUNT with LIKE Operator
You can use the SQL COUNT() function along with the LIKE operator to perform the wildcard pattern matching. F you don’t know the full names or you want the customer count of the city name to start with C or something else, you can use this. For example, the below query will find and count the total number of customers in DimCustomer whose name starts with J.
SELECT COUNT(*) AS CustomersJ
FROM DimCustomer
WHERE FirstName LIKE 'J%';
SQL COUNT function with CASE WHEN
If you use the COUNT function with the CASE WHEN statement, it allows you to calculate the total number of records that match a specific expression mentioned in the CASE. It is very helpful when you want to count the customers whose sales or order values are in different thresholds (ranges). For example, the below query will count the total employees whose sales are less than 600, sales between 600 and 2000, and employee sales between 2000 and 4500. To count how many employees fall in each case. Remember, there is one record above 4500.
SELECT
COUNT( CASE WHEN [Sales] < 600 THEN EmpID END) AS 'Low Performers'
WHEN [Sales] > 600 AND [Sales] < 2000 THEN EmpID END) AS 'Average Performers'
WHEN [Sales] >= 2000 AND [Sales] < 4500 THEN EmpID END) AS 'Good Performers'
FROM [Employees]
Similarly, the below query will count the total number of customers in the DimCustomer table whose age is greater than 25. If the condition is TRUE, it returns 1; otherwise, NULL is returned. As we all know, the COUNT function doesn’t consider the NULL values. So, the output will be non-null customers whose age is greater than 25.
SELECT COUNT(CASE WHEN DATEDIFF(yyyy, [BirthDate], GETDATE()) > 25 THEN 1 ELSE NULL END) AS TotalAdults
FROM DimCustomer;
SELECT COUNT(CASE WHEN DATEDIFF(yyyy, [BirthDate], GETDATE()) > 50 THEN 1 ELSE NULL END) AS TotalAdults
FROM DimCustomer;
JOIN Example
If you observe the above queries that we wrote against the AdventureWorksDW, you can see we have performed multiple joins. To understand the joins, we use this section with a simple two tables and display the result.
Using the COUNT function along with the Joins helps you count the records of multiple tables. In this example, we are using the Inner Join to join the Employee and Department table. Next, we compute the total records grouped by Department name.
SELECT E.DeptID, dep.DepartmentName,
COUNT(*) AS [Total Records]
FROM [Employees] E
INNER JOIN Department dep ON E.DeptID = dep.DeptID
GROUP BY E.DeptID, dep.DepartmentName
It is another example to demonstrate the Joins along with this SQL COUNT function.
SELECT E.Education, dep.DepartmentName,
COUNT(*) AS [Total Records]
FROM [Employees] E
INNER JOIN Department dep ON E.DeptID = dep.DeptID
GROUP BY E.Education, dep.DepartmentName
ORDER BY Education
Multiple tables Example
You can use this Count function on Multiple Tables. The below query will compute all the records from the Employee table and Department table.
-- Multiple Tables Example SELECT (SELECT COUNT(*) FROM [Employees] ) AS [Total Employees], (SELECT COUNT(*) FROM [Department] ) AS [Total Departments]
Using COUNT with SUM and AVG
This example shows how we can use the SQL COUNT function along with other aggregate functions like SUM() and AVG() to calculate the sum and average. It first groups the employees by their education qualifications and then counts the total employees in each group, sum and average of their sales.
SELECT Education, COUNT(*) AS Employees,
SUM(Sales) AS Sale, AVG(Sales) AS Average
FROM [Employees]
GROUP BY Education
The results will be
| Education | Employees | Sale | Average |
| Bachelors | 4 | 10596.52 | 2649.13 |
| Education | 2 | 9288.58 | 4644.29 |
| Graduate Degree | 2 | 2294.52 | 1147.26 |
| High School | 1 | 2319.99 | 2319.99 |
| Masters Degree | 2 | 4640.98 | 2320.49 |
| Partial College | 1 | 699.0982 | 699.0982 |
| Partial High School | 3 | 73.48 | 24.4933 |
Using Window Function
The below show example query will count the orders per Customer using Window Function.
SELECT cust.CustomerKey, cust.FirstName + cust.LastName AS Name,
fact.SalesOrderNumber, fact.OrderDate,
COUNT(s.SalesOrderNumber) OVER (PARTITION BY cust.CustomerKey) AS OrdersPerCustomer
FROM FactInternetSales fact
JOIN DimCustomer cust ON fact.CustomerKey = cust.CustomerKey
ORDER BY cust.CustomerKey, fact.OrderDate;
Common SQL COUNT Errors and How to Fix Them
- If you want the total rows in a table, use the COUNT(*). Instead of this, if you use the COUNT(ColumnName), it won’t count the NULL records in the total and if there are many NULLs in that particular column, you won’t get the number you expected.
- While using the DISTINCT keyword, always follow it with the column name because it does not support *. For instance, SELECT COUNT(DISTINCT *) is wrong. However, SELECT COUNT(DISTINCT Email Address) FROM employee is the correct one.
- Please be careful while working with GROUP BY. You must place all the selected columns that are not part of the aggregate function (COUNT) within the GROUP BY. For instance, SELECT education, COUNT() FROM employees returns an error. However, SELECT education, COUNT() FROM employees GROUP BY education; will be the correct format.
- In SQL Server, you are not allowed to use the aggregate COUNT function within the WHERE clause to filter the data. In such a scenario, use the HAVING clause and t allows to use any aggregate function. For instance, if you add the WHERE condition to the previous example, it throws an error.
-- ERROR
SELECT education, COUNT(*) FROM employees
GROUP BY education WHERE COUNT(*) > 10
--CORRECT Approach
SELECT education, COUNT(*) FROM employees
GROUP BY education HAVING COUNT(*) > 10