subquery inside a case statement
If I understood you correctly, all you need is to calculate max(total_deposit) and join it to your main table, it can be done by using CTE or, if you wish, a subquery, like this:

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john'); 

declare @t table(username varchar(100), total_deposit int); 
insert into @t 
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

with tot_dep as
(select username, max(total_deposit) as max_d
 from @t
 group by username) 

select s.username,
       case when t.max_d &gt;= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join tot_dep t
        on s.username = t.username;  


or (that is the same)

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john'); 

declare @t table(username varchar(100), total_deposit int); 
insert into @t 
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

select s.username,
       case when t.max_d &gt;= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join  (select username, max(total_deposit) as max_d
                     from @t
                     group by username) t
        on s.username = t.username;