php - Using LEFT JOIN with 2 different query GROUP BY -

my first sql query is:

select username,count(username) total_active_users users active = '1' group referrer //  $act_user 

to count active users each referrer

the second sql query is:

select count(orders) total_user_orders users_orders group $act_user['username'] // username first query. 

to count total number of orders each user got first query

what i'm trying using left join count active users have 1 order @ least

the problem is: have 2 different group by first 1 "referrer" , second "username"

i'm trying :

select u.username, count(u.username) total_active_users, count(b.orders)  total_user_orders users u left join users_orders b on u.username = b.username u.active = '1' , total_user_orders >= '1' group (u.referrer u) , (b.username b) 

any idea please?

you can instead:

select    a.username,    count(u.username) total_active_users,    count(b.orders) total_user_orders  (    select username users_orders total_user_orders >= '1'     union    select username users        u.active = '1'  ) left join users        u on a.username = u.username left join users_orders b on u.username = b.username group a.username;