CODEWITHSUNDEEP
mysqlsqlcount
ceno980
ceno980

Reputation: 2011

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?

Upvotes: 1

Views: 72

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would just use aggregation:

select industry, max(name) as name
from stores
group by industry
having count(*) = 1;

If there is only one name, then max(name) is the one.

If names can be repeated in the table, then:

having min(name) = max(name)

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use EXISTS :

SELECT s.*
FROM Stores s
WHERE EXISTS (SELECT 1 FROM Stores s1 WHERE s1.Industry = s.Industry AND s1.Name <> s.Name);

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use corelated subquery

select s.* from stores s
where exists ( select 1 from Stores s1 where s.Industry=s1.Industry
                               having count(*)=1)

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

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

Upvotes: 1

Related Questions