0

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

2 Answers 2

1

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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.