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.
