Count(*) vs (ColumnName)

Posted on 2010-09-14
Last Modified: 2012-05-10
in the article on the author uses the following SQL code:

SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Is there any reason why they did not use count(*) instead of count(email) ?
Question by:Mr_Shaw
LVL 73

Accepted Solution

sdstuber earned 125 total points
ID: 33674977
count(*) counts rows

count(email) counts rows that have a non-null value for email

Assisted Solution

ronan1979 earned 125 total points
ID: 33674980
the query is all about "email" parameter - for an homogenised formula
LVL 41

Assisted Solution

ralmada earned 125 total points
ID: 33674985

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(email) evaluates expression for each row in a group and returns the number of nonnull values
Different result
more info 
LVL 69

Assisted Solution

Qlemo earned 125 total points
ID: 33675192
You could use this equivalent:

SELECT email,
 COUNT(*) AS NumOccurrences
FROM users
where email is not null
GROUP BY email
HAVING ( COUNT(email) > 1 )


Author Closing Comment

ID: 33680652

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question