Get data using sum without where

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.

table

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.

Recommended Articles