Consider the following database table named water_schemes :
The number of tuples returned by the following SQL query is
with total(name, capacity) as select district_name, sum(capacity) from water_schemes group by district_name with total_avg(capacity) as select avg(capacity) from total select name from total, total_avg where total.capacity >= total_avg.capacity
Explanation: First group by district name is performed and total capacities obtained as following
Ajmer 20 Bikaner 40 Charu 30 Dungargarh 10
Then average capacity is computed,
Average Capacity = (20 + 40 + 30 + 10)/4 = 100/4 = 25.
Finally districts with more than average are selected.
Bikaner is 40 which is greater than average (25) Charu is 30 which is also greater than average (25). Therefore answer is 2 tuples.