SELECT uid ,sum(ordr_amt) ordr_amt ,avg(cmmc_amt) cmmc_amt FROM order_info a join ( SELECT friend_uid ,sum(ordr_amt) cmmc_amt from order_info group by 1 ) b on a.uid = b.friend_uid group by 1