SELECT c.mileage, SUM(amount)
FROM claims c
INNER JOIN invoice_info i
ON i.ID = c.ID AND i.accident_date = c.accident_date
GROUP BY c.mileage
HAVING COUNT(*) > 1
but it has the same effect as previous query!
tables structure is that each claim has multiple invoices...
But, that's not the query I gave you. I gave you this query:
Code:
SELECT c.ID, count(*)
FROM claims c
INNER JOIN invoices i
ON i.ID = c.ID AND i.accident_date = c.accident_date
GROUP BY i.ID
having count(*) > 1
Which is intended to show any invoices with multiple claims.
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.
Does that answer the question? Or have I misunderstood the need?
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
Which query gave you what output, as opposed to what you need?
... 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.
Reply With Quote
Originally Posted by mawais31
Bookmarks