Using Count(col1)??
-
Hi,
I don't know if this is possible or not, but I really hope it is...
The goal is to find out if there are duplicate IDs being imported. I won't go into the detail of the messed up data, but suffice it to say that I know there are duplicate IDs, but I don't want to hard code those IDs because we'd like to be able to use this again.
I figured I could just count the IDs, and leave out the ones that were not equal to 1. So, I would like to do something to the effect of:
SELECT Count(No), No
FROM Request
WHERE COUNT(No) <> 1
GROUP BY No
WITHOUT the WHERE clause, the results would be something like:
1, 345
1, 346
2, 347
3, 348
1, 349
etc...
I know you can't use the Count in the WHERE... so is there a way to accomplish this? I'd like to incorporate this statement into an UPDATE statement so that it will update field1 in the table to 0 if the count equals something other than 1. (So where No is 347 and 348, field1 would be set to 0, while 345, 346,and 348 would remain 1.) Then when I do the import, I'll just pull all the records where the value of field1 is not 0.
I'd like to keep this as simple as possible, too. It's ultimately going into a SQL task in a DTS.
Thanks in advance for any help!!
-
SELECT Count(No) as Total, No
FROM dbo.Request
GROUP BY No
HAVING COUNT(No) 1
-
Awesome! Thanks... I knew it was something simple!!!
-
Yeah, when using aggregate function such as COUNT, MAX and SUM,, always change the WHERE clause into HAVING
Ezz Khayyat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply