Gaylen,

Add this as a field to your query:

sum(iif(losses > 500000, 1, 0)) as big_loss_cnt

HTH,
Chad

>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