Demystifying SQL Counts: Mastering COUNT(*) and COUNT(column)
Here's a table to illustrate the difference:
ID | Name |
---|---|
1 | Alice |
2 | Bob |
3 | (NULL) |
COUNT(Name)
would return 2 (only rows with a name are counted).
Choosing the right one:
- Use
COUNT(*)
when you need the total number of rows in the table, including those with NULL values. - Use
COUNT(column_name)
when you want to count the rows with a specific value (not NULL) in a particular column.
SELECT COUNT(*) AS total_rows
FROM customers;
This code will count all the rows in the customers
table, regardless of whether the columns have values or not. It will return a single row with the total number of customers.
Example 2: Counting rows with a value in a specific column
SELECT COUNT(email) AS email_count
FROM customers;
This code will count the number of rows in the customers
table where the email
column has a value (not NULL). This tells you how many customers have a valid email address entered.
Example 3: Using WHERE clause with COUNT(*)
SELECT COUNT(*) AS active_users
FROM users
WHERE status = 'active';
This code uses COUNT(*)
with a WHERE
clause to specifically count the number of rows in the users
table where the status
column is equal to 'active'. This helps you determine the number of active users.
SELECT SUM(row_count) AS total_rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('your_table_name');
Important points about DMVs:
- This method can be less performant than
COUNT(*)
because it might involve additional processing on the database side. - The specific DMV syntax and availability can vary depending on the database system you're using.
- It's generally not recommended for widespread use due to potential performance drawbacks and portability issues.
sql