×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Finding Duplicates In Table

Finding Duplicates In Table

Finding Duplicates In Table

(OP)
Hi All!

I'm sure this will be a very basic piece of sql for most of you.  I am just starting out in the world of development and have come across a problem that I need help with.

Basically, I have one table ( tblInvoice ) and I need to find duplicates within.  

The tblInvoice has three columns –
invoiceID (int)
ClaimID (varchar)
IsPartial (bool)

There may be multiple invoices for a single ClaimID

I need to find the total number of invoices grouped by ClaimID where IsPartial = True and there are more than ONE partial invoice for that ClaimID

Any help will be greatly appreciated and please don't hesitate to ask any questions if I have not explained it properly.

Thanks!
 

RE: Finding Duplicates In Table


True, this is a very basic piece of sql for most, therefore you should try coding the query yourself and if you get stuck we may help you out. Otherwise, how are you going to learn? 3eyes
 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

RE: Finding Duplicates In Table

(OP)
I have had a go myself using the GROUP BY and HAVING clause but no cigar.  I will paste in a copy of what I have tried so far tomorrow for you to take a look at.

Thanks

RE: Finding Duplicates In Table

(OP)
Here's one I have tried:

SELECT tblInvoice.ClaimID, COUNT(tblInvoice.InvoiceID) AS TOTAL_INVOICES
FROM tblInvoice
GROUP BY tblInvoice.ClaimID
HAVING COUNT(tblInvoice.InvoiceID) > 1
AND tblInvoice.IsPartial = 'TRUE'


I get the following error, which I understand but I can't figure out where else it could possible go:

'Column 'tblInvoice.IsPartial' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.'

RE: Finding Duplicates In Table

(OP)
Great - works perfectly, thanks!

I wasnt aware that you could use both WHERE and HAVING in the same query.  Noob mistake.

Thanks all

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close