JOIN two CTE to create a summary using GROUP BY

I have a CTE called open_bridges whose first 24 rows are shown below.

I use the following SQL Developer query to get the following result:

SELECT 
            
      oba.Structure_type,
            
     SUM(CASE WHEN oba.YEAR_BUILT<='2016' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2016,
     SUM(CASE WHEN oba.YEAR_BUILT<='2017' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2017,
     SUM(CASE WHEN oba.YEAR_BUILT<='2018' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2018,
     SUM(CASE WHEN oba.YEAR_BUILT<='2019' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2019,
     SUM(CASE WHEN oba.YEAR_BUILT<='2020' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2020,
     SUM(CASE WHEN oba.YEAR_BUILT<='2021' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2021,
     SUM(CASE WHEN oba.YEAR_BUILT<='2022' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2022,
     SUM(CASE WHEN oba.YEAR_BUILT<='2023' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2023
            
    FROM open_bridges oba 
   
    
    GROUP BY oba.structure_type,gika.structure_type
    ORDER BY oba.structure_type DESC

The result is:

Then I run another SQL query:

SELECT 
            
            gika.Structure_type,
            
            SUM(CASE WHEN EXTRACT(YEAR FROM gika.status_granted_date) >='2016' THEN 1 ELSE 0 END) AS gik2016

FROM gikl_all_strucs gika
GROUP BY gika.structure_type

I get following result:

However, its combining both results that gives wrong values.

The SQL query I tried is as follows:

SELECT 
            
     oba.STructure_type,
     SUM(CASE WHEN oba.YEAR_BUILT<='2016' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2016,
     SUM(CASE WHEN EXTRACT(YEAR FROM gika.current_status_granted_date) >='2016' THEN 1 ELSE 0 END) AS gik2016,            
     SUM(CASE WHEN oba.YEAR_BUILT<='2017' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2017,
     SUM(CASE WHEN oba.YEAR_BUILT<='2018' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2018,
     SUM(CASE WHEN oba.YEAR_BUILT<='2019' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2019,
     SUM(CASE WHEN oba.YEAR_BUILT<='2020' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2020,
     SUM(CASE WHEN oba.YEAR_BUILT<='2021' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2021,
     SUM(CASE WHEN oba.YEAR_BUILT<='2022' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2022,
     SUM(CASE WHEN oba.YEAR_BUILT<='2023' OR oba.YEAR_BUILT IS NULL THEN 1 ELSE 0 END) AS y2023
             
FROM open_bridges oba LEFT JOIN gikl_all_strucs gika 
                ON oba.structure_type=gika.structure_type 
                        AND oba.structure_no=gika.structure_no 
                        AND oba.year_built=gika.year_built 
                        AND oba.structure_status_type_code=gika.structure_status_type_code
    
    
    GROUP BY oba.structure_type
    ORDER BY oba.structure_type DESC

and the result I get is:

I get '0' in gik2016 column.

Can anyone please assist me in rectifying my SQL query so I can get the following result:

*Please note that open_bridge and gikl_all_strucs are two CTEs derived from the same table. The only difference is a WHERE caluse. open_bridge deals with structure type IN ('F') whereas gikl_all_strucs is structure type IN ('G','I','K','L').

I shall be obliged if some one can help me write a SQL query to get the result I want.

My apologies if I am not able to explain my question properly. If you require more information please let me know.

UNION both tables together and then pivot:

SELECT Structure_type,
       SUM(CASE WHEN type = 'GAS' AND year_built >=2016 THEN 1 ELSE 0 END) AS gik2016
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2016 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2016,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2017 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2017,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2018 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2018,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2019 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2019,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2020 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2020,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2021 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2021,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2022 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2022,
       SUM(CASE WHEN type = 'OB' AND (YEAR_BUILT<=2023 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2023
FROM   (
  SELECT structure_type,
         year_built,
         'OB' AS type
  FROM   open_bridges
  UNION ALL
  SELECT structure_type,
         EXTRACT(YEAR FROM status_granted_date),
         'GAS'
  FROM   gikl_all_strucs
  WHERE  status_granted_date >= DATE '2016-01-01'
)
GROUP BY
      structure_type

Or, if both open_bridges and gikl_all_strucs are views based on the same table then just use the base table:

SELECT Structure_type,
       SUM(CASE WHEN structure_type IN ('G','I','K','L') AND year_built >=2016 THEN 1 ELSE 0 END) AS gik2016
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2016 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2016,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2017 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2017,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2018 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2018,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2019 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2019,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2020 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2020,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2021 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2021,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2022 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2022,
       SUM(CASE WHEN structure_type = 'F' AND (YEAR_BUILT<=2023 OR YEAR_BUILT IS NULL) THEN 1 ELSE 0 END) AS y2023
FROM   your_base_table
GROUP BY
      structure_type