Getting Random Results Creating Table

I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.

Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...

If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:

select * from
(select claim_nbr,sum(amtpay) paysum from my_table
group by claim_nbr
order by claim_nbr) m,
(select claim_nbr,sum(amtpay) paysum from new_table
group by claim_nbr
order by claim_nbr) n
where
m.claim_nbr = n.claim_nbr and
m.paysum <> n.paysum;

It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.

Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.

Here is the backwards way around using min that drops whole claims sometimes but works fine other times:

CREATE table new_table
as
(select claim_nbr,other data...,amtpay,ymdeff,ymdpaid
from
(select claim_nbr,other data... ,sum(amtpay) amtpay
from my_table
group by claim_nbr,other data...

[Code] ...........