Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

COUNT(*) vs. COUNT(ColumnReference)

Most of us are familiar with this but one of my team members recently got stuck with his query. The difference is COUNT(*) includes NULL values while COUNT(column_reference) doesn’t. As an example consider the two below queries for Northwind database that find the number of orders placed by each Northwind customer:

WRONG:
SELECT C.CompanyName, COUNT(*) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
ORDER BY NoOfOrders

RIGHT:
SELECT C.CompanyName, COUNT(O.OrderId) AS NoOfOrders
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
ORDER BY NoOfOrders

Advertisements

One response to “COUNT(*) vs. COUNT(ColumnReference)

  1. Jithu October 8, 2010 at 8:48 pm

    The below query also works well:
    SELECT C.CompanyName, COUNT(1) AS NoOfOrders
    FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
    ON C.CustomerID = O.CustomerID
    GROUP BY C.CompanyName
    ORDER BY NoOfOrders

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: