Thanks I tried the iif conditional earlier and I must have had the syntax wrong and gave up on it.

You get three stars. Unfortunately I gave Chad 3 and I didn't know you could only give out three stars once. So I gave you the one remaining star I had. I guess the board moderator want us to do everything in moderation.


As alway I appreciate your quick response.

Thanks Again
Gaylen



>

>... SUM( IIF(incurred > 500000, 1, 0) ) AS whatever ...
>

>>I have a table with policyNo,FundYr,Incurred as fields.
>>
>>I can get the count and sum of the incurred(losses) by policy and FundYr but I want to know how to get the count of losses over 500,000 in the same sql statement.
>>
>>

>>select policyNo,FundYr,sum(incurred)as incurred,count(*)as total_cnt from claims group by policyNo,FundYr ..........................
>>
>>

>>
>>In the past I created a second cursor for over 500000 incurred losses and then joined it to the above cursor. I tried a number of IIF variations but to no success.
>>
>>I know I am throwing up a soft ball and someone will hit it out of the park probably in record time :-).
>>
>>Thanks
>>Gaylen