www.webdeveloper.com
Results 1 to 12 of 12

Thread: Sql problems in large databases (Help)????

Hybrid View

  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Sql problems in large databases (Help)????

    I have a large database, Where I have two tables claims and invoices. In invoice table when I run the query

    select sum(amount) from invoices.
    Gives me value of 207859507

    However when I join two tables and group then by one variable such as mileage in claim table


    SELECT c.mileage, SUM(i.amount) AS amount
    FROM claims c
    INNER JOIN invoices i
    ON i.ID = c.ID AND i.accident_date = c.accident_date
    GROUP BY mileage

    it gives me a greater value i.e. 214110863...

    But conceptually the join must have smaller value then the other query

    Regards

  2. #2
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    You've got to be seeing multiple claims/invoice there. Try this:

    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
    See if anything pops up.

  3. #3
    Join Date
    Jul 2012
    Posts
    6

    Re:

    Well thanks for your quick feedback!

    I currently run

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

  4. #4
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    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.

  5. #5
    Join Date
    Jul 2012
    Posts
    6
    Well actually I want to get the frequency for mileage (category) variable vs invoice amount

    Simply saying that I want to sum all invoice amount with car mileage 0, 1, 2,3,4,5 and group them in following form

    i.e.

    mileage body_amount
    0 25134337
    1 16142050
    2 28038785
    3 23138071
    4 6813811
    5 9133636

  6. #6
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Ok. Does the query I provided return any results?

  7. #7
    Join Date
    Jul 2012
    Posts
    6
    yes it does...


    ID count(*)
    0 2
    639077 2
    1468482 2
    1564561 2
    1612265 2
    1688768 2
    1699746 2
    1710960 2
    1715252 2
    1725834 2
    1726617 2
    1729543 2
    1732168 2
    1738369 2
    1747632 2
    1754493 2

  8. #8
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    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?

  9. #9
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    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
    Note the change to a LEFT join.

  10. #10
    Join Date
    Jul 2012
    Posts
    6
    Thanks for your time, and help

    your query give me following output,

    however I need
    mileage sum(amount)
    0 25134337
    1 40000
    2 500

    I also have noticed that ID and accident date are not unique in my claim table, there are almost 450 records of ID = 0 in Claims table ....

    your query output
    ID count(*)
    2098634 7442
    0 2
    639077 2
    1468482 2
    1564561 2
    1612265 2
    1688768 2
    1699746 2
    1710960 2
    1715252 2
    1725834 2
    1726617 2
    1729543 2
    1732168 2
    1738369 2

    and so on.........

  11. #11
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Quote Originally Posted by mawais31 View Post
    your query give me following output,
    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.

  12. #12
    Join Date
    Jul 2012
    Posts
    6
    thanks for your help! I got the point and since if we cannot represent the record with primary key then it would be difficult to join the table....

    Regards
    Awais khan

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles