Count(*) or Count(1) vs Count(Column Name) in SQL Statement

Introduction

In SQL script, developer came across to show the total values by using COUNT (Expresion). There are always debate among developers whether should use 1 or * as an expression. Both of them maybe wrong for the result. Better to use correct column name. 

Using the code

Using AdventureWorks2012 database, if you want to know “How many orders were placed for each product”, you will need to  use left join SalesOrderDetail with Product table. There are some products which don’t have any order and you need to show 0 for those.

SQL Script

select  p.ProductID
    , count(p.ProductID)       AS Count_ProductID
    , count(*)                 AS Count_Asteric 
    , count(1)                 AS Count_One
    , count(s.SalesOrderID)    AS Count_SalesOrderID
from [Production].[Product] p
left join sales.SalesOrderDetail s
    on s.ProductID = p.ProductID
group by p.ProductID
order by p.ProductID

Results from the SQL Script

From the result, you can see, Count_ProductID, Count_Asteric and Count_one are returning wrong result. Because, there is no order for some of the ProductID (e.g., 532, 534, etc.)

If you have null value for rows, and you want to show zero, then you should specify the proper column in the count expression.  

Points of Interest

We are always confused, whether we should use Count(1) or Count(*). From this example, you can determine what we should use.