SQL: Selecting all tuples which contain a certain value in a column after using count

I have the following table called Stores:

Name   |   Category   |   Industry 

ABC         appliances      retail 
XYZ         banking         finance 
NZE         clothing        retail
JKI         tutoring        education

I would like to output all the Names that are the only one in their Industry (e.g. XYZ and JKI are the only Names in their Industry).

I have the following query:

select s.Name, s.Industry, a.Number
from Stores s
inner join (
select Industry, count(*) as Number
from Stores group by Industry
) a
on s.Industry = a.Industry;

I get an output table which has an attribute called Number which gives the total number of times each Industry appears in the table Stores. How can I select all the tuples which have the value of 1 in the Number column after using the inner join?

Answer

use where condition

select s.Name, s.Industry, a.Number
from Stores s
inner join (
select Industry, count(*) as Number
from Stores group by Industry
) a
on s.Industry = a.Industry where a.Number=1

Advertisement

Answer