[posted and mailed, please reply in news]

Julio Allegue (jallegue@med.miami.edu) writes:
> This the end result that I want.
>
> CheckNum Account Name Addr1 City State Zip SUM of
> Max(Notes)
> of Cost or
> Min(Notes)
> --------------------------------------------------------------------------
>>---
> 12345 11111 John 123 Mary St Miami FL 33139 123 Great
> 54321 22222 Mary 321 River St Clifton NJ 07055 24 Fine
> 98765 33333 Tom 12 Main St Miami FL 33139 3 I like it

There is a general rule for this sort of questions what you should post
for this sort of question.

o CREATE TABLE statements for your tables.
o INSERT statements with sample data
o The output given the sample data.

If you do that, it is very easy by means to cut and paste to not only
write a solution, but also to *test* it.

One thing is not wholly clear from your post. Do you want the min/max
of the Note itself, or the Note that goes with the min/max cost? Your
text says the former, but your sample output indicates the latter, and
this is what I have assumed.

Thus, the suggestion that follows is untested, and you will have to refine
it yourself:

SELECT ci.CheckNum, ci.Account, ci.Name, ci.Addr1, ci.City,
ci.State, ci.Zip, s.Costsum, ca.Notes
FROM ClientInfo ci
JOIN (SELECT CheckNum, Account,
Costsum = SUM(Cost), mincost = MIN(Cost)
FROM ClientAcct
GROUP BY CheckNum, Account) as s ON ci.Account = s.Account
AND ci.CheckNum = s.CheckNum
JOIN ClientAcct ca ON ca.Account = ci.Account
AND ca.CheckNum = ci.CheckNum
AND ca.Cost = s.Cost

There is one thing that is disturbing with this solution: if there are
more than two or more rows that are tied for the min value, then you
will get all those rows. Since ClientAcct does not seem to have a unique
key, this is quite difficult to address.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp