TOP 1 WITH TIES: Let Us Count them!

Wednesday, August 18, 2004

Let Us Count them!

Lets have a quick look on the differences between using COUNT(*) and COUNT(colname). Also discuss other ways to get the count of rows.

1. COUNT(*) returns the number of total rows. Count(colname) returns the total number of rows with Non NULL value for colname. If the specified column doesnt allow NULLs the result will be same.

eg. (USE PUBS)

SELECT count(*) FROM Titles
-- Returns 18

SELECT count(title_id) FROM Titles
-- Returns 18

SELECT count(royalty) FROM Titles
-- Returns 16

When you are doing Count on a column with NULLs SQL server will give you the warning. "Warning: Null value is eliminated by an aggregate or other SET operation."

As Count(colname) excludes columns with NULL values, the following query

SELECT count(royalty) FROM Titles where royalty IS NULL

will return zero. If you want to get the count of rows with NULL in a specific column, you should use,

SELECT count(*) FROM Titles where royalty IS NULL

which returns two in this case.

2. COUNT(colname) supports the use of ALL/DISTINCT clause (ALL is the default). COUNT(*) doesn't. So to get the UNIQUE Royalty values, you can use the query

SELECT COUNT(DISTINCT royalty) FROM Titles

You can combine these to see the difference.

SELECT COUNT(*), COUNT(royalty), COUNT(DISTINCT royalty) FROM Titles

3. As COUNT(*) returns the total number of records (in each group when GROUP BY is present in the query), like SELECT @@ROWCOUNT, SELECT COUNT(*) is also a valid statement in its own, which always returns 1.

SELECT COUNT(*)
GO

4. Performance. (with due credits to Itzik Ben Gan) If the specified column name does not allow NULLS, there isn't any performance difference between using COUNT(*) or COUNT(colname). The Optimizer will choose the smallest index available to get the number of records.

SELECT COUNT(*) FROM Titles
---------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))

SELECT Count(type) FROM Titles
---------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind]))

As the type column doesnt allow NULLS, both the counts return the same number and SQL Server uses the smallest index available.

If you issue the COUNT(colname) on a column that allows NULL, the execution plan might be different, as in

SELECT count(notes) FROM Titles
---------------------------------------------------------------------------------------
--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
--Stream Aggregate(DEFINE:([Expr1003]=COUNT_BIG([titles].[notes])))
--Clustered Index Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind]))

In this case, SQL Server will use a nonclustered index, if one is defined on the specified column. Otherwise a clustered index scan(as in the above case) or a table scan will be performed.

Here you can also notice that SQL Server internally replace the COUNT() function with COUNT_BIG() function. COUNT_BIG works like the COUNT function. The only difference between them is their return values: COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

5. Conditional Count
Using the CASE Expression, you can get count of items conditionally. See the following example.

Use NothWind
Go
SELECT COUNT(*) as ProductCount,
COUNT(CASE WHEN DisContinued = 0 Then 1 END) As Available,
COUNT(CASE WHEN DisContinued = 1 Then 1 END) As Discontinued
FROM Products

6. Getting Count from multiple tables
Here is two methods to get the count of rows from more than one table.

SELECT SUM(cnt) as EmployeesandCustomers FROM
(SELECT COUNT(*) cnt FROM Employees
UNION
SELECT COUNT(*) cnt FROM Customers) T

SELECT (SELECT COUNT(*) cnt FROM Employees)+
(SELECT COUNT(*) cnt FROM Customers) as EmployeesandCustomers

7. Count('AnythingGoesHere')
The parameter for the count function can be a column name, number, literal or an expression. The BOL says that "expression can be of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted."

SELECT COUNT(1) or SELECT COUNT('abc') is equivalent to SELECT COUNT(*)

8. Alternatives.
If you don't require an exact answer, it isn't necessary use a SELECT count(*) query on the rows in a table to get the row count, because it might be a time consuming process to get the count of rows from a table with millions of rows. SQL Server keeps the row count in sysindexes and it can be retrieved there. But it may not always reflect the exact number of records.

See below.

SELECT rows as linecount
FROM sysindexes
WHERE id = OBJECT_ID('Titles') AND indid <> 2

If you have a tip on count, plz post a comment.

# posted by Roji. P. Thomas @ 8/18/2004 09:26:00 PM

Comments:

your query is incomplete

SELECT rows as linecount
FROM sysindexes
WHERE id = OBJECT_ID('Titles') AND indid <> WHAT???

# posted by

Partha Mandayam : 10:44 PM

Thanks for pointing out the typo, I had corrected it.

# posted by

Roji. P. Thomas : 11:29 PM

DBCC UPDATEUSAGE(0) - this would keep uptodate information in sysindexes.

Moreover DBCC CheckTable('TableName') also gives us the rowcount

# posted by

Anonymous : 3:23 PM

This is such a great resource that you are providing and you give it away for free. Great post...
thanks for sharing the info....
thebestandroid

# posted by

thebestandroid : 5:30 PM

According to sources, iPad 3 will release next month in a small lot followed by a larger release in September.iphone 5 rumors have set the media guessing about the iphone 5 release date and what hardware update it will
have. It’s not just the iphone 5 release date, there’s also an ever-growing list of people’s expectations about the iphone 5. Recent reports show that there are some features that will not be showcased in the iphone 5.

# posted by

iphone 5 : 12:56 PM

Thanks for pointing out the typo, I had corrected it.

# posted by

Madiha Shoukat : 7:40 PM

Really fantastic article regarding the project. I just really love it. Thanks for making a wonderful share!

# posted by

galaxy note 2 cases : 1:34 PM

SAP on cloud & Navision on cloud by Adapt Software India.

AshishKamotra, Chief Executive Officer, Tally on cloud Adapt, Web enabling software in India by Software India Pvt. Ltd., India Soft-2012, HICC, Hyderabad, Andhra Pradesh. Running windows applications on IPad running windows applications on Android Adapt specializes in Microsoft SharePoint, Navision on cloud, Microsoft Dynamics CRM, Sage CRM, Microsoft .Net, MS SQL & Power Builder.

SAP on cloud
Web enabling software in India
Running windows applications on iPad
Running windows applications on Android
Navision on cloud

# posted by

Adapt software India : 1:05 AM

Running windows applications on Android, Running windows applications on I pad &
SAP on cloud by Adapt Software India.
AshishKamotra, Chief Executive Officer, Running windows applications on Android, Running windows applications on I pad by Software India Pvt. Ltd., India Soft-2012, HICC, Hyderabad, Andhra Pradesh. Adapt specializes in SAP on cloud Microsoft Dynamics CRM, Sage CRM, Microsoft .Net, MS SQL & Power Builder.

SAP on cloud
Running windows applications on iPad
Running windows applications on Android
Go-Global
Remote access software

# posted by

Adapt software India : 12:13 PM

Local internet marketing & Business Consulting
The Volpé Consortium, Inc. is an independent business consulting and project management firm specializing in the areas of Business Operations, Project Management, Technology Solutions, and Training. Since our founding, our mission has been to partner with clients to integrate conflict-free consulting and deep subject matter expertise for senior management, resulting in sustainable solutions to complex business challenges.Our services have been proven to drive success across multiple industries and business disciplines.
local internet marketing
internet marketing blog

# posted by

Unknown : 8:14 PM

# posted by

Adapt software India : 5:56 PM

Go-Global , Running windows applications on Android, Running windows applications on I pad.
SAP on cloud by Adapt Software India. Ashish Kamotra, Chief Executive Officer, Running windows applications on Android, Running windows applications on Ipad by Software India Pvt. Ltd., India Soft-2012, HICC, Hyderabad, Andhra Pradesh. Adapt specializes in SAP on cloud Microsoft Dynamics CRM, Sage CRM, Microsoft .Net, MS SQL & Power Builder.
Go-Global
Remote access software
SAP on cloud
Tally on cloud
Navision on cloud
web enabling software in india

# posted by

Adapt software India : 4:41 PM

http://uncleplastic.blogspot.com/2005/05/review-westone-um2.html

http://toponewithties.blogspot.com/2004/08/let-us-count-them.html
http://trik-tips.blogspot.com/2008/04/cara-membuat-dropdown-menu.html?showComment=1319897468184#c8830314559671865632
http://ifeelsicktomystomach.blogspot.com/2009/10/october-18th.html
http://www.wissel.net/blog/d6plinks/SHWL-6MHCLB
http://indiawebsearch.com/content/best-easter-egg-hack-for-windows-seven-called-the-god-mode#comment-368

# posted by

Adapt software India : 12:20 PM

# posted by

Adapt software India : 12:20 PM

Nick Bathla, owner of YO! Creations, began as a search engine optimizer, digital marketing consultant quickly discovered he had a sixth sense for marketing. Nick decided to launch his own company.

SearchEngineOptimization
Search engine Marketing
Facebook Marketing
Social Media Marketing
Facebook Store
Online Marketing
Online Advertising
Internet Marketing
SEO
SEM

# posted by

Aliya : 6:07 PM

inventory journal

operation software

director software

driver software

factory software

pub software

baker software

baker software

# posted by

jaycutler : 12:29 AM

I really like your article. It’s evident that you have a lot knowledge on this topic. Your points are well made and relatable.

# posted by

Clipping path service : 11:20 PM

Free WordPress Themes from templatedesk.com- Site Templates and CMS Themes.

Template Desk

# posted by

Template Desk : 3:32 PM

Great Work!!! Really like your techniques and ideas! this template is really have an awesome look. Thanks my friend :)
I am a designer at clipping path service Cheers! :)

# posted by

George : 1:02 AM

Amazing writing. Thanks a lot

# posted by

Oscar Fernandez : 3:23 PM

I really enjoyed your blog Thanks for sharing such an informative post.

# posted by

Clipping Path Service : 8:03 PM

A great blog. I really like blogs of this type. IM very impressed.
caredit

# posted by

Farhan Tanvir : 5:29 PM

Wow! this is a great and helpful piece of info.Ghost Mannequin services
Photo Retouching Services
background removal service

# posted by

David Millar : 11:46 AM

This is the best blog! This article is very comfortable. It is an amazing post you have published here. I have seen your post carefully and liked it. Hope that you will share such effective post regularly, thanks
Photo Editing

# posted by

Clipping Path Solve : 6:38 PM

just wow. uff

# posted by

How to Use the Photoshop magic wand tool : 2:11 PM

TOP 1 WITH TIES

Wednesday, August 18, 2004

Let Us Count them!

About Me

Links

archives