title: Update working sql with count (*), st_intersect and query TO count actual numbers by an group_id
body: <p>I have a working sql that gives me new fields using select count(*) per unique_id in stable_area(table_with_polygon) with query to change_area(table_with_polygons.
  p.date2 is actually from change_area.</p>
  
  <pre><code>SELECT p.group_name, p.date, p.date2, p.group_id, p.polygons_per_group_id,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) AND COALESCE(impact,1) = 1 AND b.date &gt; date2 as low_impact,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) AND impact = 2 AND b.date2 &gt; date as medium_impact,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) AND impact = 3 AND b.date2 &gt; date as high_impact,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) AND impact = 4 AND b.date2 &gt; date as xtrahigh_impact,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) AND b.date &gt; date2 as total_impact,
  FROM stable_area p ORDER BY p.group_name
  </code></pre>
  
  <p>What I want is to find out the actual numbers per group_id, the ebove query needs to be done to each individual polygon from stable_area, and then summarize the impacts per group_id.</p>
  
  <p>I tried ST_JOIN before running query, but that obviously did not work since the query of dates did not end up because all individual stable_area has different dates.
  Also All polygons in stable_area are overlapping.</p>
  
  <p>So lets say change_area has 8 polygons where date2 on 4 of them has 20200315, and 4 of them has 20200409 and there is 2 stable_area polygons with same group_id that 1st polygon has date 20200310 and second has 20200405, in this scenario all change_area are within both polygons from stable_area with same group_id
  then run </p>
  
  <pre><code>SELECT p.group_name, p.date, p.date2, p.group_id, p.polygons_per_group_id,
  (select count(*) from change_area b where ST_Intersects(p.shape,b.shape) and COALESCE(impact,1) = 1 AND b.date2 &gt; p.date) as low_impact,
  FROM stable_area p ORDER BY p.group_id
  </code></pre>
  
  <p>then get the sum of of change_area of individual stable_area with same group_id.
  the result from above give me 12 in count, but I should never get more then 8 change_area polygons in total, and probably less since date could differ more.</p>
  
  <p>So in this case the total change_area impacts per group_id in stable area should be 8.</p>
  
  <p>I am working in Postgres/postgis</p>
  
  <p>I am a beginner.
  Thank you</p>