select r.id, r.name,
sum(p.crop_area) as crop_area_sum,
sum(p.total_gross) as total_gross_sum
total_gross_sum*10/crop_area_sum as total_normative
from placement p
right join region r on p.region_id = r.id
where r.region_type = 2 and r.parent_id = 255
group by r.id;
In the above code, I want to use the variables I got as crop_area and gross_product without summing again to find total_normative. But it is giving an error.
enter image description here
select r.id, r.name,
sum(p.crop_area) as crop_area_sum,
sum(p.total_gross) as total_gross_sum,
cast(sum(p.total_gross)*10/sum(p.crop_area) as decimal(16, 2)) as total_normative
from placement p
right join region r on p.region_id = r.id
where r.region_type = 2 and r.parent_id = 255
group by r.id;
If I write in the form above, it works correctly. But since I have a lot of columns that are calculated like this, I re-SUM them, so I want to use the previously obtained variables crop_area_sum and total_gross_sum.
>Solution :
It is not allowed to use aliases in the same select.
To avoid having to recalculate the totals, use your initial query as a subquery and then perform the operation to retrieve total_normative :
select id, name, total_gross_sum*10/crop_area_sum as total_normative (
select r.id, r.name,
sum(p.crop_area) as crop_area_sum,
sum(p.total_gross) as total_gross_sum
from placement p
right join region r on p.region_id = r.id
where r.region_type = 2 and r.parent_id = 255
group by r.id
) as s