I need help in performing mathematical operations on the arguments I have selected in SQL

Question

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.

Answer 1

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