I’m trying to create a query to get all data (for the general, obtain the ten first values, using limit and offset). The problem shows up, when the front request wants to show the data at the first time, and these always don’t need a filter.
The query is the next
select f.farm_name, l.lot_name, c.crop_name,
c.crop_real_plants::integer, l.lot_area, c.crop_invest,
sum(ap.actpay_cost)::numeric as total_payroll,
sum(am.actmach_cost)::numeric as total_machine,
sum(asp.actsup_cost)::numeric as total_supplies,
sum(cd.cost_detail_value)::numeric as total_cost_detail,
sum(pc.category_qty) as total_kilos
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join activities a on c.crop_id = a.crop_id
inner join activities_machines am on a.activity_id = am.activity_id
inner join activities_payrolls ap on a.activity_id = ap.activity_id
inner join activities_supplies asp on a.activity_id = asp.activity_id
inner join costs_details cd on c.crop_id = cd.crop_id
inner join productions p on c.crop_id = p.crop_id
inner join production_categories pc on p.production_id = pc.category_production
I hope to get the sum values, and use the next formulas:
- crop_invest / lot_area
- activities_consolidate / crop_real_plants
- activities_consolidate / productions_consolidate
Another query I try is this
CREATE or replace VIEW view_costos AS
select 'act' as type, c.crop_id, f.farm_name, l.lot_name, c.crop_name,
coalesce(c.crop_real_plants::integer, 0) as real_plants, l.lot_area, c.crop_invest,
am.actmach_id, am.actmach_cost
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join activities a on c.crop_id = a.crop_id
inner join activities_machines am on a.activity_id = am.activity_id
--where c.crop_id = 3
union
select 'act' as type, c.crop_id, f.farm_name, l.lot_name, c.crop_name,
coalesce(c.crop_real_plants::integer, 0) as real_plants, l.lot_area, c.crop_invest,
ap.actpay_id , ap.actpay_cost
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join activities a on c.crop_id = a.crop_id
inner join activities_payrolls ap on a.activity_id = ap.actpay_id
--where c.crop_id = 3
union
select 'act', c.crop_id, f.farm_name, l.lot_name, c.crop_name,
coalesce(c.crop_real_plants::integer, 0) as real_plants, l.lot_area, c.crop_invest,
asp.actsup_id, asp.actsup_cost
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join activities a on c.crop_id = a.crop_id
inner join activities_supplies asp on a.activity_id = asp.actsup_id
--where c.crop_id = 3
union
select 'cost', c.crop_id, f.farm_name, l.lot_name, c.crop_name,
coalesce(c.crop_real_plants::integer, 0) as real_plants, l.lot_area, c.crop_invest,
cd.cost_detail_id, cd.cost_detail_value
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join costs_details cd on c.crop_id = cd.crop_id
--where c.crop_id = 3
union
select 'prod', c.crop_id, f.farm_name, l.lot_name, c.crop_name,
coalesce(c.crop_real_plants::integer, 0) as real_plants, l.lot_area, c.crop_invest,
pc.category_id, pc.category_price
from crops c
inner join lots l on c.lot_id = l.lot_id
inner join farms f on l.farm_id = f.farm_id
inner join productions p on c.crop_id = p.crop_id
inner join production_categories pc on p.production_id = pc.category_production;
--where c.crop_id = 3;
With the before query, I got the values respective, but can sum() these, any idea to make this process?
The idea is got the sum() values of all crops there are will be in the table, and sum() all these, respect the crop_id like the query shows.
