/* Advanced SQL
Nested aggregate queries
*/ 





/* Nested aggregate  */

/* 1st: Detailed data about crop plantings and 
the harvest yield count. */

select CropPlantingID,crop, variety,
   cast(Yield_Count as decimal) as yld_count
   from tblcropVariety CV, tblcropPlanting CP, tblCropHarvest CH
   where CV.CropVarID = CP.CropVarID
   and CP.CropPlanting_ID = ch.CropPlantingID;


/* 2nd: Aggregate planting and harvest data */

select crop, variety, 
  count(cropPlantingID) as planting_count,
  sum(yld_count) as yield_sum
from
  (select CropPlantingID,crop, Variety,
     CAST(Yield_Count as decimal) as yld_count
   from tblcropVariety CV, tblcropPlanting CP, tblCropHarvest CH
   where CV.CropVarID = CP.CropVarID
   and CP.CropPlanting_ID = ch.CropPlantingID) cvph
group by crop, variety
order by yield_sum desc;


/* 3rd: Aggregate based on crop type. */

select crop_type, 
  count(*) as "# of Crops Planted", 
  avg(yield_sum) as "Avg Crop Yield Count"
from tblCrop, 
  (select crop, variety, 
   count(cropPlantingID) as planting_count,
   sum(yld_count) as yield_sum
   from
    (select CropPlantingID,crop, Variety,
     cast(Yield_Count as decimal) as yld_count
     from tblcropVariety CV, tblcropPlanting CP, tblCropHarvest CH
     where CV.CropVarID = CP.CropVarID
     and CP.CropPlanting_ID = ch.CropPlantingID) cvph
     group by crop, variety) SubQ
where tblCrop.Crop = SubQ.Crop
group by crop_type
order by crop_type;