Database Administrators Asked on December 24, 2021
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
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.
Answered by Lennart on December 24, 2021
There is no need to define inner query, try this,
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
Answered by turivishal on December 24, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP