Click here to Skip to main content
15,888,610 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
1.64/5 (6 votes)
14 Jul 2016CPOL 13K   2   2
Count(*) or Count(1) vs Count(Column Name)

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

C++
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

Image 1Image 2

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
United States United States
Team Lead and Application Developer in a wide variety of business application development. Particularly, interested in team managing and architecting application. Always, interested in solving complex problem.

Specialties: Team management, Agile, .Net Framework, Java, Database Design, MVVM, MVC, Enterprise Library, Prism

Comments and Discussions

 
QuestionThe difference is not because of COUNT argument, but because of LEFT JOIN Pin
Rick Drizin17-Jul-16 11:53
Rick Drizin17-Jul-16 11:53 
I couldn't see your screenshot, but the difference on the numbers is more about the LEFT JOIN than about the argument passed to the COUNT. As Paw Jershauge explained, COUNT(*), COUNT(1) and COUNT(column) are totally identical to each other. But only as long as column is not null, because COUNT does not count NULLs.
Since you are LEFT JOINing Products with OrderDetails, you will get AT LEAST one row for each product, possibly more if there are OrderDetails on that product. That´s why COUNT(p.ProductID), COUNT(*) and COUNT(1) will all have the same result: the number of rows which have that ProductID, because it´s key of the left join.
COUNT(s.SalesOrderID) on the other hand may show different results when OrderDetails does not have that ProductID. In that case, ProductID will have only one row on the LEFT part of the join, OrderDetails will be null on the RIGHT side of the join, and COUNT on any column from the right side will return NULL.
In the bottom line, either all COUNTs from your query will have the same value (when there is one or more orders including that product), or the first 3 counts will have value 1 (because there is ONE of that Product, forced on the left join) and the last count will have value 0 (because there is NO Order including that product).
I think your article could have explained better why there are differences on the counts, specially the NULL and the LEFT join thing.
QuestionWhen in doubt use COUNT(*) there's Absolutely NO overhead on it... Pin
Paw Jershauge14-Jul-16 21:36
Paw Jershauge14-Jul-16 21:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.