Finding Duplicates In Table

[sqlsnip]

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!

 

[LKBrwnDBA]

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?


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

 

[PHV]

In fact, what have you tried so far and where in your SQL code are stuck ?
Tip: have a look at the HAVING clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sqlsnip]

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

 

[sqlsnip]

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.'

 

[PHV]

SELECT ClaimID, COUNT(*) AS TOTAL_INVOICES
FROM tblInvoice
WHERE IsPartial = TRUE
GROUP BY ClaimID
HAVING COUNT(*) > 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sqlsnip]

Great - works perfectly, thanks!

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

Thanks all