SQL COUNT Function with example

Count function takes one parameter and returns the number of rows for the matching criteria.

Select Count(*) From employees

It will return count of entire records into Employees table, same result can also be get from following sql statement

Select Count(1) From employees

In this statement we used, 1 in place of , there are discussion about count() and count(1), which will work fast, some people say * try to get the column names from table while in case of count(1), no need to evaluate the column name so count(1) will work fast. I don’t want to go in detail about count(*) and count(1) in this article.

What is the different between count(*) and count(column_name)

• Count(*) or Count(1) will always returns the count entire rows
• Count(columnx) will return the count of all those records where columnx is not null means count(column_name) ignores those records where column value is null

Let’s say we have a table “users” with following records

useid       firstname   lastname    
101        A           B
102        A           NULL 
103        X           Y

Select count(*) as [star]
 , count(1) as [one]
 , count(useid) as [id_count]
 , count(firstname) as [fname_count] 
 , count(lastname) as [lname_count]
 , count(password) as [pass]
From users   
    --  Result     
Star    one id_count    fname_count lname_count
 3    3   3           3           2

As I said, count(column name) ignores NULL value records, that's why lname_count is 2.

We can also count distinct value column, say, we want to count the unique first name then we can use

Select count(distinct firstname From users

and result will be 2.

One thing we need to know, if we will use count then other columns must be grouped by otherwise we will get error.

Also we can not use count(xxx) in where class like

select * from users where count(email) > 1

it will give error:

But we can use

  Select email from users 
  Group By email
  Having count(email) > 1

Other blogs you may like

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,427

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 683

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,843

Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma?... By Ali Adravi   On 08 Feb 2013  Viewed: 17,407

Error cannot be caught by a TRY…CATCH Construct in SQL Server

TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi   On 28 Dec 2012  Viewed: 589