If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Claims with multiple invoices shouldn't be an issue. In that case, you're just repeating values from the claims table in the result set. However, if there happens to be an invoice with multiple claims against it, your invoice amount sum() will be greater than the sum() of the invoices alone.

What exactly is this query trying to show? I don't understand what information you're after.

So, what you're seeing are invoices with multiple claims attached to them. I'm not sure what the business relationship between a claim and an invoice is for you folks, so I can't tell you precisely how to handle this.

If it's appropriate for an invoice to have multiple claims on it, you may need to adjust your query to just pick the claim with the highest mileage category. Something like this:

Code:

SELECT
(select MAX(c.mileage) from claims c where i.ID = c.ID AND i.accident_date = c.accident_date) as mileage,
SUM(i.amount) AS amount
FROM invoices i
GROUP BY mileage

Or maybe the lowest mileage category is what you're most interested in:

Code:

SELECT
(select MIN(c.mileage) from claims c where i.ID = c.ID AND i.accident_date = c.accident_date) as mileage,
SUM(i.amount) AS amount
FROM invoices i
GROUP BY mileage

Or, if you think you shouldn't be seeing multiple claims on an invoice, someone needs to correct the problem before you can get accurate numbers here.

Let me attach a little disclaimer to what I said. What I think you're seeing are invoices with multiple claims. It would appear from your column names that you're not using the ID as traditional PK's. So, if I'm wrapping my head around this correctly, you could also be seeing both claims with multiple invoices and invoices with multiple claims in the result set of my query.

This query, I think, is more accurate:

Code:

SELECT c.ID, count(*)
FROM claims c
LEFT JOIN invoices i
ON i.ID = c.ID AND i.accident_date = c.accident_date
GROUP BY i.ID
having count(*) > 1

... Did you see the bit where I said, "if you think you shouldn't be seeing multiple claims on an invoice, someone needs to correct the problem before you can get accurate numbers here."

I'm not sure there's any more help I can really provide. Unless one of the min/max queries I posted gives you the appropriate results, I'm tempted to say your data just isn't set up to provide the results you need. That is, someone needs to fix the data first.