Using query I provided it will be:

;with Sums AS (select	sum(amount) as Total, ;
					member_ref, ;
					description ;
			from payments ;
				where description like '%Test%' ;
				group by member_ref, description ;
			),
Numbered as (select *, row_number() over (partition by member_ref ORDER BY Total DESC) as Rn from Sums)

select CASE WHEN Total<10 then 'Tier1' WHEN Total >=10 and Total < 20 then 'Tier2' ELSE 'Tier3' END as Tier,
member_ref, description, Total from Numbered where Rn = 1

If you prefer to use Gregory's query you will need to use 2 derived tables.


>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