Records not adding up! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Records not adding up!

Author

Topic

paulballard
Starting Member

1 Post

Posted - 2005-08-23 : 06:33:11

I have this query which returns 604 records. When I add an extra where clause: i.LDL_CLIENT_CHECK = 1, I get 134 records. When I change it to i.LDL_CLIENT_CHECK = 0 I get 501 records. The problem is when i add both of these up I don't get the original 604 but 635. LDL_CLIENT_CHECK is a bit field.

Any ideas why?

select i.LDL_EXTRACT_DATE,
COUNT(i.id) AS Extracted
FROM
dbo.tbl_iWorldImport i INNER JOIN
dbo.tbl_ClaimAssigned a ON i.id = a.idiWorldImport
WHERE
(i.LDL_CLIENT_CHECK IS NOT NULL)
GROUP BY i.LDL_EXTRACT_DATE

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-23 : 08:57:49

When you say "it returns x records", are you saying that literally that many rows are returned in the results, or that the sum of the COUNT() column ("Extracted") is equal to x?

The # of rows returned in the result will vary based on the values in the table and the grouping that is performed, since by definition using GROUP BY alters the # of rows returned as the data is summarized.

Consider this table:

ID, Bit
--, ---
A, 1
B, 0
C, 1
C, 0

And look at the following results (similiar to your SELECT):

select id, count(*) from tbl where Bit = 1 group by ID -- returns 2 rows

select id, count(*) from tbl where bit = 0 group by ID -- returns 2 row

select id, count(*) from tbl group by ID -- returns 3 rows

Note that 2 + 2 <> 3 ....

edit notes (to appease JHermiz): Added some highlighting to the code and fixed a grammatical error.

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-23 : 09:32:37

quote:
Originally posted by paulballard

I have this query which returns 604 records. When I add an extra where clause: i.LDL_CLIENT_CHECK = 1, I get 134 records. When I change it to i.LDL_CLIENT_CHECK = 0 I get 501 records. The problem is when i add both of these up I don't get the original 604 but 635.

This isn't a problem (unless your data is wrong). Some of the records have the exact same value of LDL_EXTRACT_DATE with different values of LDL_CLIENT_CHECK and are GROUPED into the same result (when not filtered).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources