SQL error getting counts of subscribes and unsubscribes number from 2 tables

Question

I'm getting an error on this Presto SQL code in subscribes and unsubscribes function and variable names:

SELECT
    emailaddress,
    firstname,
    lastnamename,
    role,
    status,
    optindate,
    optinsource,
    optoutdate,
    optoutreason,
    SUM(subscribes) AS total_subscribes,
    SUM(unsubscribes) AS total_unsubscribes
FROM  
    (SELECT
         emailaddress,
         firstname,
         lastnamename,
         role,
         status,
         optindate,
         optinsource,
         optoutdate,
         optoutreason,
         1 as subscribes
     FROM 
         table_subscribes
     UNION ALL
     SELECT
         emailaddress,
         firstname,
         lastnamename,
         role,
         status,
         optindate,
         optinsource,
         optoutdate,
         optoutreason,
         1 AS unsubscribes
     FROM 
         table_unsubscribes) all
GROUP BY
    emailaddress, firstname, lastnamename,
    role, status, optindate, optinsource,
    optoutdate, optoutreason,

I would like to get sum of total subscribes and total unsubscribes from each table combined and group by other attributes into resulting table

Answer 1

UNION will only keep the column namaes of the first query, the rest gets lost, so add all columns to both query of the UNION and all will be fine

SELECT
  emailaddress,
  firstname,
  lastnamename,
  role,
  status,
  optindate,
  optinsource,
  optoutdate,
  optoutreason,
 SUM(subscribes) as total_subscribes,
 SUM(unsubscribes) as total_unsubscribes
FROM(
      SELECT
       emailaddress,
       firstname,
       lastnamename,
       role,
       status,
       optindate,
       optinsource,
       optoutdate,
       optoutreason,
       1 as subscribes,
       0 as unsubscribes
      FROM table_subscribes
     UNION ALL
     SELECT
       emailaddress,
       firstname,
       lastnamename,
       role,
       status,
       optindate,
       optinsource,
       optoutdate,
       optoutreason,
       0 as subscribes,
       1 as unsubscribes
      FROM table_unsubscribes
)all
GROUP BY
emailaddress,
  firstname,
  lastnamename,
  role,
  status,
  optindate,
  optinsource,
  optoutdate,
  optoutreason

If you want a totle of all subscribers yyou need, with out GROUp BY

SELECT
 SUM(subscribes) as total_subscribes,
 SUM(unsubscribes) as total_unsubscribes
FROM(
      SELECT
       emailaddress,
       firstname,
       lastnamename,
       role,
       status,
       optindate,
       optinsource,
       optoutdate,
       optoutreason,
       1 as subscribes,
       0 as unsubscribes
      FROM table_subscribes
     UNION ALL
     SELECT
       emailaddress,
       firstname,
       lastnamename,
       role,
       status,
       optindate,
       optinsource,
       optoutdate,
       optoutreason,
       0 as subscribes,
       1 as unsubscribes
      FROM table_unsubscribes
)all