How to use subqueries to combine columns-CodePudding

I have a table(mainusertb) as follows,

select * from mainusertb order by UserID asc;

UserID  Username    Status
1        Mike       Active
2        Ann        Active
3        Michel     Active
4        John       Active
5        Anonymous  Active

Further I have some other table(emprevntb):

Subusers    Revenue Hour
Mike_1          32  3
Mike_2          34  4
Ann_3           11  5

and from that I am running this query:

SELECT SUBSTRING_INDEX(subusers,'_',1) AS user, 
        SUM(COALESCE(Revenue,0)) AS charge 
  FROM emprevntb 
  WHERE Hour BETWEEN '3' AND '5' 
    AND SUBSTRING_INDEX(subusers,'_',1) IN 
       (SELECT Username FROM mainusertb WHERE STATUS='Active') 
 GROUP BY SUBSTRING_INDEX(subusers,'_',1);

from that I could able to get following result,

user    charge
Ann       11
Mike      66

But I need to order them with asc order and all the users as well. Here what I need.

user    charge
Mike      66
Ann       11
Michel     0
John       0
Anonymous  0

Can someone show me where to change?

CodePudding user response：

Join from the user table to the other table containing the data, then aggregate by user:

SELECT
    u.Username,
    COALESCE(SUM(e.Revenue), 0) AS charge
FROM mainusertb u
LEFT JOIN emprevntb e
    ON SUBSTRING_INDEX(e.subusers, '_', 1) = u.Username AND
       e.Hour BETWEEN '3' and '5'
GROUP BY
    u.Username;