Easily Display Record Counts in SQL Server Using COUNT(*) OVER()

The SQL COUNT(*) OVER () analytic function is a powerful tool that allows you to include a total record count within each row of your query results. This feature is especially valuable when you want a quick, overall count alongside detailed row data without adding extra logic or breaking the query into separate parts. By using COUNT(*) OVER (), you can efficiently generate a total count for the entire result set, making it easier to produce summaries in reporting or analysis.

A great scenario for using COUNT(*) OVER () is displaying customer information alongside a count of all customers. With this function, you can retrieve detailed data for each customer and, at the same time, include a column that shows the total customer count in every row. This allows for clear and concise data presentation, demonstrating how COUNT(*) OVER () simplifies counting while keeping your query streamlined.

Scenario: Listing Customers with a Total Record Count
Retrieve a list of customer information and include a column showing the total count of all customers in the result set. Rather than using a separate query to get this count, we can use COUNT(*) OVER () to display it directly alongside each row.

Sample Query

1. Select several columns from the customers table.
2. Join it with the orders table to show each customer’s recent orders.
3. Use COUNT(*) OVER () to display the total number of customers within each result row.

select 
[cust].[customer_id],
[cust].[first_name],
[cust].[last_name],
[orders].[.order_id],
[orders].[.order_date],
count(*) over () as [total_customer_count]
from  [dbo].[customers] as [cust]
join  [dbo].[orders] as [orders] on [cust].[customer_id] = [orders].[customer_id]
order by [cust].[last_name]

Query Explanation
COUNT(*) OVER (): This function calculates the total count of records for the query result set and adds it to each row. Every row will display the same total, making it easy to see the record count at a glance.
JOIN statement: We join the customers table with the orders table to show each customer's order details.
ORDER BY cust.last_name: Sorting by last name is optional but can improve readability in large result sets.

The Output

Each row includes detailed information about the customer and their order, along with a "total_customer_count" column that shows the total number of customers in the query (250 in this example). This method keeps the query streamlined and efficient by including the count directly in the results.

As a Bonus
To set a variable equal to TRUE when "Total_Record_Count" is greater than 1 without breaking the query or adding extra logic, simply can use a conditional IIF expression with the COUNT(*) OVER() analytic function in the SELECT clause directly. Using the IIF function is very useful for this type of conditional check. Here is the modified query:

select 
[cust].[customer_id],
[cust].[first_name],
[cust].[last_name],
[orders].[.order_id],
[orders].[.order_date],
count(*) over () as [total_customer_count],
-- set = TRUE (1) if Total_Record_Count > 1, else FALSE (0)
iif(count(*) over () > 1, cast(1 as bit), cast(0 as bit)) as [Is_Multiple_Records]
from  [dbo].[customers] as [cust]
join  [dbo].[orders] as [orders] on [cust].[customer_id] = [orders].[customer_id]
order by [cust].[last_name]

Query Explanation
The IIF function checks if COUNT(*) OVER () is greater than 1. If true, it sets the "Is_Multiple_Records" calculated field to 1 (TRUE), otherwise to 0 (FALSE).

Benefits of Using COUNT(*) OVER ()
Efficiency: Shows the record count without requiring additional queries or subqueries.
Readability: Keeps the query structure clean, especially helpful for reporting.
Versatility: Useful for all SQL Server-compatible databases, making it suitable for complex queries with joins or filters.

Conclusion
The COUNT(*) OVER () function in SQL Server is an efficient way to include the total record count in query results. This function provides a clean, effective way to present row data alongside total counts, simplifying analysis and reporting.