SQL query for where there is only one occurrence of an item-CodePudding

I'm trying to learn SQL and I have a small sample table: Table name is 'artist'

 name                    | nationality
 --------------------------------------
 Nura Nal                | Naltorian    
 Susan Storm             | American    
 Tyson Gilford           | American    
 Kendra Sanders          | American    
 Natasha Romanoff        | Russian    
 T'Challa                | Wakandan    
 Matthew Michael Murdoch | American    
 Karen Beecher-Duncan    | American    
 Kathy Kane              | American    
 Kimberley Ann Hart      | American   
 Reed Richards           | American    
 Barbara Gordon          | American   
 Ethan Morris            | Droolian    
 Barry Allen             | American

Can anyone tell me what command would return the name of artists where their nationality appears only once. Basically in the instance above it will be excluding all americans and only displaying the other nationalities

CodePudding user response：

Grouping will collapse multiple rows down to a single row. You're only interested in those with a single name value so a dummy min() or max() will extract the name that you want.

select min(name) as name
  -- , nationality -- this column is still available if desired
from artist
group by nationality having count(*) = 1

CodePudding user response：

First you have to find which nations appear only once, then use that set to filter the whole table. something like this:

with
    nations as (
        select nationality, count(*) c
        from artists
        group by nationality
        having count(*) = 1
    )
select a.*
from artists a
join nations n on n.nationality = a.nationality

CodePudding user response：

If you want to see the nationality as well, you can use a window function

select name, nationality
from (
  select name, nationality, 
         count(*) over (partition by nationality) as cnt
  from artist
) a 
where cnt = 1  
;