title: Find the max value when there are multiple groups to be considered
body: <p>I have already tried <a href="https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results">Get records with max value for each group of grouped SQL results</a> answers from here. But it takes only group by one column. I need several. So I am posting the question here.</p>
  <p>I have an dataset(usertable) as follows,</p>
  <pre><code>ID  Username    Status
  1   John        Active
  2   Mike        Active
  3   Ann         Deactive
  4   Leta        Active
  5   Lena        Active
  6   Lara        Active
  7   Mitch       Active
  </code></pre>
  <p>Further I have a revenuetb table as follows which has substrings of each user.</p>
  <pre><code>subuser           hour          Revenue
  John_01         2/26/2022 5:00     5
  Mike_01         2/26/2022 7:00     8
  Mike_01         2/26/2022 7:00     22
  Leta_03         2/26/2022 7:00     67
  Leta_07         2/26/2022 9:00     56
  Mitch_07        2/26/2022 11:00    34
  </code></pre>
  <p>I need to match each subuser with main User(ex:John_01==John) in usertable and get the sum of revenue of each user as well. But if the Hour is repeated for each subuser I need to get the maximum Revenue of that each sub user.</p>
  <p>As an example I need to get the revenue with 22 and should neglect 8.</p>
  <pre><code>Ex:
      Mike_01         2/26/2022 7:00     8
      Mike_01         2/26/2022 7:00     22
  </code></pre>
  <p>So final table should looks like below.</p>
  <pre><code>User    Total_Usage
  John         5
  Mike         22
  Leta         123
  Lena         0
  Lara         0
  Mitch        34
  </code></pre>
  <p>Here is my try.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT u.Username
       , COALESCE(SUM(Revenue), 0) AS TOTAL_USAGE
    FROM usertable u
    LEFT JOIN revenuetb e
      ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username
     AND e.Hour BETWEEN 'XXX' and 'XXX' 
   where u.STATUS='Active'       
   GROUP BY u.Username 
   order by u.ID
  ;
  </code></pre>
  <p>But this is not neglecting repeated Hours. Can someone explain how should I do this?</p>
  <p>Limitation :cannot use window functions in my remote server.</p>