|
paulballard
Starting Member
United Kingdom
1 Posts |
 Posted - 08/23/2005 : 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
USA
7402 Posts |
Posted - 08/23/2005 : 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. |
Edited by - jsmith8858 on 08/23/2005 09:00:09 |
 |
|
Records not adding up!
Forum Locked
