Different in count of records.

Question

Post reply

Different in count of records.

Kumara1011

SSC-Addicted

Points: 424
More actions
April 22, 2013 at 4:15 am

#292424

Hi All,
when i am trying to find the count of records, i am getting two different counts.
example:
select * from table
--1500
select count(*) from table
--1500
select count(*),sum(total) from table
--1450 5681665
can any help on this, why we are getting difference?
thanks in advance,
Kumar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

darren.morgan 32557 Newbie Points: 6 More actions · Answer 1

Could it be ignoring rows that have a null value for the field you are performing sum on?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

darren.morgan 32557 (4/22/2013)
Could it be ignoring rows that have a null value for the field you are performing sum on?

-- No

SELECT COUNT(*), SUM(Total)

FROM (

SELECT ID = 1, total = 10 UNION ALL

SELECT ID = 2, total = 10 UNION ALL

SELECT ID = 3, total = 10 UNION ALL

SELECT ID = 4, total = NULL UNION ALL

SELECT ID = 5, total = NULL UNION ALL

SELECT ID = 6, total = NULL UNION ALL

SELECT ID = 7, total = 10 UNION ALL

SELECT ID = 8, total = 10 UNION ALL

SELECT ID = 9, total = 10 UNION ALL

SELECT ID = 10, total = 10

) d

-- Yes

SELECT COUNT(Total), SUM(Total)

FROM (

SELECT ID = 1, total = 10 UNION ALL

SELECT ID = 2, total = 10 UNION ALL

SELECT ID = 3, total = 10 UNION ALL

SELECT ID = 4, total = NULL UNION ALL

SELECT ID = 5, total = NULL UNION ALL

SELECT ID = 6, total = NULL UNION ALL

SELECT ID = 7, total = 10 UNION ALL

SELECT ID = 8, total = 10 UNION ALL

SELECT ID = 9, total = 10 UNION ALL

SELECT ID = 10, total = 10

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kumara1011 SSC-Addicted Points: 424 More actions · Answer 3

If we take specific column in the count function, its fine.

But when i am using count(*), it showing difference.

i found that in background one command is executing i.e.,

DBCC checkDB('Databasename'). I guess it showing impact on that select query, as we are using the same DB.

Does any suggest what are the problems we will be facing while this command is executing "DBCC checkDB "?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Kumara1011 (4/22/2013)
If we take specific column in the count function, its fine.
But when i am using count(*), it showing difference.
i found that in background one command is executing i.e.,
DBCC checkDB('Databasename'). I guess it showing impact on that select query, as we are using the same DB.
Does any suggest what are the problems we will be facing while this command is executing "DBCC checkDB "?

It's nothing to do with DBCC checkDB and it's not a bug or error, it's expected behaviour. From BOL:

"COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden