Trouble with SQL query using COUNT

Question

list days appearing in reservation where only red boats have been reserved using COUNT aggregate function. Here is the table

servant table:

sname rating
Joe 4
Bob 2
Tim 9
Mike 1
Lewis 5

boat table:

bname color rating
Ace orange 6
Bethany red 5
Cruiser green 9
WindySea red 8

reservation table:

sname bname day
Bob  Ace Monday
Bob  Bethany Wednesday
Bob  WindySea Saturday
Tim  Ace Sunday
Tim  Bethany Wednesday
Tim  Cruiser Wednesday
Mike Ace Monday

A description of how to convert NOT EXISTS to COUNT would also be helpful. — ghosting999
– ghosting999, Commented Oct 24, 2016 at 20:54

ghosting999 · Accepted Answer · 2016-10-24 21:19:47Z

1

SELECT r.day 
FROM reservation r
JOIN boat b
  ON r.bname = b.bname
GROUP BY r.day 
HAVING COUNT(CASE WHEN b.color <> 'red' THEN 1 ELSE NULL END) = 0
   AND COUNT(CASE WHEN b.color = 'red' THEN 1 ELSE NULL END) > 0 -- optional

edited Oct 24, 2016 at 21:19

ghosting999

10112 bronze badges

answered Oct 24, 2016 at 21:00

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan Carlos Oropeza Over a year ago

Sean ELSE NULL is the default if you didnt included.

ghosting999 Over a year ago

I see, this is a very clean of working this out. I tried other ways but always ran into an extra column that shouldn't have been included. thanks!

Collectives™ on Stack Overflow

Trouble with SQL query using COUNT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related