Hey

I have a table with per agent, per month an amount. I want to sum all the amounts on the biggest month per agent

I have a query as follows
select (select ru1.amount from ru ru1 where ru1.enddt = max(ru.enddt) and ru1.agentid = ru.agentid
from r
where ru.enddt <= adat
group by agentid

In that case a receive per agent teh amount in the biggest month. But I 1 amount (the sum of this output

Thanks

Try this.

SELECT SUM(amount)
FROM Ru AS R
WHERE enddt <= adate
AND enddt =
(SELECT MAX(enddt)
FROM Ru
WHERE enddt <= adate
AND agentid = R.agentid)

If that's not it, please post DDL, sample data INSERTs and show your
required result.

--
David Portas
SQL Server MVP
--