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

Question

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

Lennart · Answer

Not sure I understand the question since I can't figure out why you want to group by app_id. The following yields the expected result, see Fiddle
select tenant_code, avg(reg_count)
from h_tenant_overview
group by tenant_code

or
select tenant_code, sum(reg_count) / nullif(count(reg_count),0)
from h_tenant_overview
group by tenant_code

The nullif in the last variant is unnecessary since count(reg_count) <> 0, but demonstrates a technique for handling division by zero.
Can you add sample data that does not return the expected result in fiddle and post back url, and I will have a look.

turivishal · 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

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

2 Answers

Add your own answers!

Ask a Question