BTW, good point about semicolons. In SQL Server you need to put ; at the end of the statement, but not in the middle. Gregory wrote VFP version of the statement with the ;

>You need to use a case statement.
>
>This is a sql server group. Does the semicolons work>
>

>select case when total <= 10 then total as tier1,
>          case when total between 10 and 20 then total as tier2 ,
>          case when total > 20 then total as tier3 ,
>			member_ref 
>		from ( 
>			select	sum(amount) as Total, 
>					member_ref, 
>					description 
>			from payments 
>				where description like '%Test%' 
>				group by member_ref, description 
>			) X 
>
>

>
>>Hi
>>
>>going with gregorys sql query
>>
>>

	
>>select max(total), ;
>>			member_ref ;
>>		from ( ;
>>			select	sum(amount) as Total, ;
>>					member_ref, ;
>>					description ;
>>			from payments ;
>>				where description like '%Test%' ;
>>				group by member_ref, description ;
>>			) X ;
>>		group by 2
>>
>>

>>
>>How could I alter that SQL to return
>>
>> 'Tier1' if the max total is up to 10
>>
>>'Tier2' max total 10 to 20
>>
>>and 'Tier3' over 20
>>
>>Thanks
>>
>>Nick