T-SQL 101: 85 Counting rows and column values with COUNT

In previous posts, I looked at how to read data from a table. Now, we need to look at how we do calculations on the data in the table.

The most basic calculation we might do is to count the number of rows in the table. The first example above does that.

What about the asterisk?

But also notice that is has an asterisk in the query. Some people worry about the asterisk being in their queries as usually having an asterisk isn’t a good idea. In fact, some customers have automated systems for checking code, and the automated system might complain about the asterisk.

You could avoid the asterisk by writing this instead:

SELECT COUNT(1)
FROM dbo.Orders;

That might look odd at first, but what that says is “return the value 1 for every row, and count the number of 1’s”. So that’s the same as the number of rows. I often write these queries with a 1 instead of an asterisk, but in the end, with SQL Server, it really is the same query.

Another Column

Look at the second query though. In that example, I’ve said COUNT(OrderComments) and you might think that would work the same.

But the first rule to learn about aggregates is that most of them ignore NULL values. So what this second query is actually saying is “count all the rows where OrderComments isn’t NULL”.

In my system, even though there are 88,640 orders in the system, and the first query returned 88640 as expected, there are only 38 that have order comments where the value isn’t null, and so the second query returned 38.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-02-15