Mysql – how to nest query with avg and sum group by outer in MySQL 5.7

MySQL

I have a dataset like this:

tenant_code app_id reg_count
  1          1       0
  1          2       0
  2          1       1
  2          2       1
  3          1       5
  3          2       5

and now I want to group by tenant_code in the outer query and sum reg_count,but the reg_count should avg app_id in the inner query first and next sum reg_count in outer, what should I do to make it work? This is the query now I am using but did not work:

select
    sum(
        select avg(b.reg_count)
        from h_tenant_overview b
        where b.tenant_code = a.tenant_code
        group by b.tenant_code,b.app_id
    ) as reg_count
from h_tenant_overview a
group by tenant_code

the result should look like this if success:

tenant_code  reg_count
  1                 0
  2                 1
  3                 5

Best Answer

There is no need to define inner query, try this,

  • added if condition in app_id count to prevent divisible by zero error
SELECT 
    tenant_code, 
    SUM(reg_count) AS sum_reg_count, 
    IF(COUNT(app_id) = 0, 0, SUM(reg_count) / COUNT(app_id)) AS avg_reg_count,
    IF(COUNT(app_id) = 0, 0, FLOOR(SUM(reg_count) / COUNT(app_id))) AS round_reg_count
FROM h_tenant_overview 
GROUP BY tenant_code

Output:

tenant_code | sum_reg_count | avg_reg_count | round_reg_count
1             1               0.5000          0
2             2               1.0000          1
3             10              5.0000          5