All 7 Aggregate Functions & GROUP BY questions
More in Aggregate Functions & GROUP BY
What are aggregate functions in SQL Server, and what are the most common ones? How does the GROUP BY clause work together with aggregate functions? How do you filter the results of a grouped query based on an aggregated value, such as only showing customers who spent more than a certain amount? How do you correctly combine WHERE and HAVING in the same query to filter both individual rows and grouped results? How do the ROLLUP and CUBE extensions to GROUP BY help you generate subtotal and grand total rows in a single query? How would you calculate a running total or a percentage of total using aggregate functions combined with window functions?
Advertisement
SQL Server › Aggregate Functions & GROUP BY

What is the difference between COUNT(*), COUNT(1), and COUNT(column_name) in SQL Server?

Intermediate

Answer

COUNT(*) and COUNT(1) both count every row regardless of NULL values and generally perform the same, while COUNT(column_name) only counts rows where that specific column has a non NULL value, which is useful when you specifically want to know how many rows actually have data in a particular field.
SELECT COUNT(*) AS TotalCustomers, COUNT(PhoneNumber) AS CustomersWithPhone
FROM Customers;
Real-world example A customer database reports both the total number of customers and, separately, how many of them actually have a phone number on file, using COUNT(*) and COUNT(PhoneNumber) together in one query.

Common follow-ups: Does SQL Server treat COUNT(*) and COUNT(1) differently in terms of performance?;How do you count only distinct values within a column?

Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)
All 7 Aggregate Functions & GROUP BY questions