I would like to aggregate data by Region, but not include rows where the same ID is found in more than one region.

In my data below, ID value of "2" is found in both the West and East regions. I want to exclude those rows from my results. This is sample data and what I've tried, but I'm not really sure how to add that Exclusion logic.

I'm using postgres.

create table table1 (
  id integer, 
  region varchar(20), 
  amount integer
  );
  
insert into table1 values 
(1, 'west', 10), 
(2, 'west', 20), 
(2, 'east', 40), 
(4, 'north', 30), 
(5, 'north', 10), 
(6, 'north', 10);

select region, count(id), sum(amount)
from table1
group by region;

But I would like the results to be:

CodePudding user response：

select   region
        ,count(*)
        ,sum(amount)
from
(
select   *
        ,count(id) over(partition by id) as cnt
from     table1
) t
where    cnt = 1
group by region

Fiddle