CODEWITHSUNDEEP
sqlcount
Stephen Horvath
Stephen Horvath

Reputation: 5588

When would you use a column name instead of * in a count?

I understand why you use COUNT(*) and COUNT(DISTINCT col), but in which cases would you use simply COUNT(col). Wouldn't COUNT(col) return the same result as COUNT(*)?

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

and

SELECT COUNT(*) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

both result in

OrdersFromCustomerID7 4

in this W3 school example.

Upvotes: 2

Views: 85

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

When you count a column that is not NULL, then the following produce the same results:

  • COUNT(*)
  • COUNT(1)
  • COUNT(column)

There is often a small potential difference in performance. The first two count rows. The third actually requires reading the column value (at least in most databases). Under some circumstances, this can require reading additional data to determine if the value is indeed not NULL. This overhead is typically quite small in an aggregation query.

The first, using *, is the original way of counting rows. The second is fine, although I prefer the first. Why? Because COUNT(1) = COUNT(2), and I find that awkward.

Upvotes: 1

Apoorva Srivastava
Apoorva Srivastava

Reputation: 388

The results vary in cases where column values can be NULL. Both also vary when it comes to performance. You can refer to this article for more details count-vs-countcol

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try this:

DECLARE @tbl TABLE(ID INT IDENTITY,SomeValue INT);
INSERT INTO @tbl VALUES(1),(2),(NULL);

SELECT *
FROM @tbl

SELECT COUNT(*) AS COUNT_Asterisk
      ,COUNT(SomeValue) AS COUNT_SomeValue
FROM @tbl

Upvotes: 1

sagi
sagi

Reputation: 40481

When you use COUNT(Colomn) It won't count nulls.

As opposed to COUNT(*) which will count each row individually no matter null or not.

Lets take this case:

ID   |  NAME
 1      John
 2      NULL
 3      Jonathan

SELECT COUNT(*) FROM Table -- return 3

SELECT COUNT(NAME) FROM Table -- return 2

Upvotes: 2

Related Questions