SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wrong result from count(*)


Wrong result from count(*)

Author
Message
christian.dahl
christian.dahl
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 4
Hi all!
Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'

If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?
Michael Earl-395764
Michael Earl-395764
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24107 Visits: 23078
By the looks of your included T-SQL, you have gone about this in the wrong way.

Please post the entire SQL statement, the DDL for the tables, and some sample data if possible. Here are some tips for posting. If you follow these, you will get a MUCH better response.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
BenBooth
BenBooth
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 124
christian.dahl (7/22/2008)
Hi all!
Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'

If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?


You might want to try to determine if using an EXISTS clause yields the same problem. If it does then you know the COUNT itself isn't the problem, but something else. It would look something like this:

SET @SQL = @SQL + ' AND EXISTS (SELECT 0x00 FROM C055800T WHERE KUNDE_NR COLLATE SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'

-Ben
SwePeso
SwePeso
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16677 Visits: 3433
Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116


N 56°04'39.16"
E 12°55'05.25"
christian.dahl
christian.dahl
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 4
the exists generates the same fault. It is really strange. Two customers who does not exists in the second table where the count and exists statement has been tried out is being returned to my page. The only customers who should be returned should be those who exists in both tables.

This is how it looks like now:
select A.EKUNDEID,
RTRIM(A.KUNDENR) as kundenr,
RTRIM(B.ANAVNB) as navn,
RTRIM(B.ABYB) as postnummerby,
RTRIM(A.AEMAIL) as email,
A.SAPKUNDENR,
B.AKONTAKT
from JB53000T A, JB53100T B
where B.EKUNDEID = A.EKUNDEID'

if(@kundenr <> '')
set @sql = @sql + ' and A.kundenr like ''' + @kundenr + ''''
...
...
other statements to complete the search for the customers
...
set @sql = @sql + ' and exists(select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
...

exec(@sql)
SwePeso
SwePeso
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16677 Visits: 3433
I posted an answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116


N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search