Mins Causing Not Part of Aggregate Function Error

[NMiller007]

I am trying to pull the minimum of two fields and then add up two other fields from the minimum record in the table. Here is the SQL:

SELECT db_history.loan_id, Min(db_history.due_dt) AS MinOfdue_dt, Min(db_history.paid_dt) AS MinOfpaid_dt, [prin_amt]+[prin_bal_amt] AS PreviousBal
FROM db_history
GROUP BY db_history.loan_id;

If I group by PreviousBal, then the query will execute, but then I get every record for that loan, not just the record with the minimum dates.

Can I do this without using a preliminary query to get the mins and then another query to pull the PreviousBal?

 

[Remou]

How about:

SELECT db_history.loan_id, Min(db_history.due_dt) AS MinOfdue_dt, Min(db_history.paid_dt) AS MinOfpaid_dt, Sum([prin_amt]+[prin_bal_amt]) AS PreviousBal
FROM db_history
GROUP BY db_history.loan_id;

 

[NMiller007]

Unfortunately, that will not work as it sums all of those two fields when they meet the criteria.

Essentially, I need to find the record that meets the requirements and then has the minimum paid_dt (and if multiple with that paid_dt, the minimum due_dt, which should find a unique record).

I even tried using an intermediate query and that wouldn't even work. I could probably do it with two intermediate queries (one to calculate each minimum), but that seems like a major workaround.

 

[Remou]

You must include each field in an aggregate expression when you choose Group By, if Sum does not suit, how about adding the two amount fields to the Group By statement:

[tt]SELECT db_history.loan_id, Min(db_history.due_dt) AS MinOfdue_dt, Min(db_history.paid_dt) AS MinOfpaid_dt, ([prin_amt]+[prin_bal_amt]) AS Expr1
FROM db_history
GROUP BY db_history.loan_id, ([prin_amt]+[prin_bal_amt]);[/tt]

It would also be possible to use nested queries, if the above does not return the results you expect.

 

[lespaul]

It would be easier if you would post some sample records and your expected results from those records. You want to find the min of the Due_Dt AND the min of the Paid_Dt and then manipulate data. Then take the information from two other fields and add them? What if the min is in two different records?

I need to find the record that meets the requirements

what are the requirements? I would expect a WHERE clause that identified the records you are looking for....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[NMiller007]

I'm sorry for being vague, but I don't want to get into privacy issues by posting too much information. Here is the SQL:

SELECT db_history.loan_id, Min(db_history.paid_dt) AS MinOfpaid_dt, Min(db_history.due_dt) AS MinOfdue_dt, [prin_amt]+[prin_bal_amt] AS PreviousBal
FROM db_history
WHERE (((db_history.trans_desc) Not In ("Posting Error","Adjust Error")) AND ((db_history.past_due_flag)="Y"))
GROUP BY db_history.loan_id, [prin_amt]+[prin_bal_amt]
HAVING (((Min(db_history.paid_dt))>(CDate(Month(Now()) & ",1," & Year(Now())))-1) AND ((Min(db_history.due_dt))<CDate(Month(Now()) & ",1," & Year(Now()))-30));

This is the data I get:

loan_id	paid_dt	due_dt	PreviousBal
96	6/1/2007	4/1/2007	$15,237.68
96	6/1/2007	5/1/2007	$15,152.73
37	6/5/2007	5/1/2007	$28,487.86
57	6/4/2007	5/1/2007	$281,234.74
57	6/5/2007	5/1/2007	$280,651.55
93	6/5/2007	5/1/2007	$112,637.19
1	6/7/2007	4/1/2007	$37,151.44
2	6/7/2007	5/1/2007	$33,531.70
3	6/7/2007	4/1/2007	$67,851.85
3	6/7/2007	5/1/2007	$67,648.82

The min has no effect, because I am grouping by the PrevBalance field. I want is to get only one record per loan and that record is the one with the earliest paid and due dates.

Thanks!

 

[lespaul]

I'm not really interested in the data you get, it's more helpful to see the raw data and what you WANT to get.

I want is to get only one record per loan and that record is the one with the earliest paid and due dates.

So, on Loan_id 96, the Paid_dt is 6/1/2007 and the due dates are 4/1 and 5/1, which one do you want to get?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[NMiller007]

I want to get the 4/1 entry. First I want to get the earliest paid date (that meets the criteria), then if they are equal, the earliest due date. Then once the earliest have been determined, the PrevBalance calculation from that record.

I have now found some rare occurances where there are multiple records for the same paid date and due date. These occur with negative transactions, so I need to sum all of the pmt amounts in these cases to verify that they are greater than 0 (if not, omit them). I think I'm goig to try again with subqueries and see if I can make some progress tomorrow.

I appreciate the help.