CODEWITHSUNDEEP
sqltupleshaving
gfppaste
gfppaste

Reputation: 1121

How to filter SQL tuples by number of members

So I'm working on an SQL problem, in which I have a bars and beer database from which I want to: list all bars which serve a beer that Mike likes, and which are frequented by more than one drinker.

The database consists of:

likes (drinker, beer)
frequents(dinker, bar)
sells(bar, beer)

So I tried:

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents HAVING COUNT(drinker) > 1)

Which didn't work... why doesn't the count(drinker) comparison work as a filter to filter out bars that have less than 2 drinkers who frequent them?

Upvotes: 1

Views: 140

Answers (3)

gdoron
gdoron

Reputation: 150293

"which are frequented by more than one drinker." <> HAVING COUNT(drinker) > 2 (1)

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') 
AND bar IN
(SELECT bar 
 FROM beer.frequents 
 GROUP BY bar
 HAVING COUNT(drinker) > 1)

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270727

Looks like you need a GROUP BY bar in the bar subquery. Otherwise, you'll effectively get COUNT(*) from beer.frequents, which isn't what you want:

SELECT bar
FROM beer.sells
WHERE beer IN 
(SELECT beer
FROM beer.likes
WHERE drinker = 'Mike') AND bar IN
(SELECT bar FROM beer.frequents GROUP BY bar HAVING COUNT(drinker) >= 2)

Upvotes: 2

JScoobyCed
JScoobyCed

Reputation: 10413

You need to 'GROUP BY' for your aggregated COUNT to work:

SELECT bar
 FROM beer.sells
 WHERE beer IN 
  (SELECT beer
    FROM beer.likes
    WHERE drinker = 'Mike'
  )
 AND bar IN
  (SELECT bar
    FROM beer.frequents
    GROUP BY bar
    HAVING COUNT(drinker) > 1
   )

Upvotes: 2

Related Questions