Help on the Group by ...
-
Hi folks,
I am a new user and i have to count the records that i have in double in a table. I used the 'Group By' but i have no success!!!
I try this following script:
Select item1, item2, item3, count(*)
FROM Table1
Group By (item1, item2, item3)
I receive and error message that said:
"...Incorrect syntax near ','... "
How can i resolve it?
-
Remove the parenthesis from the group by.
See Books On Line for more info on group by clause
/Kenneth
-
Since you are looking for duplicates, you can use the HAVING clause to return only those records which appear more than once. This will save you the trouble of looking through the output by hand :
Select item1, item2, item3, count(*)
FROM Table1
Group By item1, item2, item3
HAVING count(*) > 1
Regards
Rob
-
I've had great luck with the following code. It's not mine, in fact, I think I got it from this forum long, long ago. It works, and that's all that's important.
SELECT *
FROM [TABLENAME]-- your table's name
WHERE
EXISTS (
SELECT NULL -- you don't need anything returned here
FROM [TABLENAME] B -- use any alias you like
WHERE B.DUPLICATE_FIELD = [TABLENAME].DUPLICATE_FIELD -- field that should be unique
GROUP BY B.DUPLICATE_FIELD
HAVING [TABLENAME].ID_FIELD < MAX(B.ID_FIELD) -- table identity field
 
Your mileage may vary!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply