UNION ALL / UNION on Presto

Question

I am using treasure data for data analytics and having trouble with union statement in presto db.

How do i do a Union All on presto. I dont understand the documentation. everytime I try to do UNION like so:

SELECT 
  COUNT(*) AS ReservationsCreated,
  resource
FROM
  reservation
WHERE
  type = 'create'
UNION
SELECT 
  COUNT(*) AS ReservationsDeleted,
  resource
FROM
  reservation
WHERE
  type = 'delete'
GROUP BY
  resource
;

I get output reformatted like:

SELECT 
  COUNT(*) AS ReservationsCreated,
  resource
FROM
  reservation
WHERE
  type = 'create'
UNION
SELECT 
COUNT(*) AS ReservationsDeleted,
resource
FROM
reservation
WHERE
type = 'delete'
GROUP BY
resource
;

and error that says:

'"resource"' must be an aggregate expression or appear in GROUP BY clause

I think I am not understanding the syntax for Presto. The docs are very confusing on Union. Any help appreciated.

Answer 1

The first part of the query is missing a group by as the error says.

 SELECT COUNT(*) AS ReservationsCreated, resource
 FROM reservation
 WHERE type = 'create'
 group by resource
 UNION ALL
 SELECT COUNT(*) AS ReservationsDeleted, resource
 FROM reservation
 WHERE type = 'delete'
 GROUP BY resource

In fact, the query could be simplified to use conditional aggregation.

select 
 resource
,sum(case when type = 'create' then 1 else 0 end) as reservationscreated
,sum(case when type = 'delete' then 1 else 0 end) as reservationsdeleted
from reservation
group by resource