SQL Group By equivalent
Posted
by
MikeB
on Stack Overflow
See other posts from Stack Overflow
or by MikeB
Published on 2013-06-26T20:20:53Z
Indexed on
2013/06/29
16:21 UTC
Read the original article
Hit count: 271
Pretend I have a cupcake_rating
table:
id | cupcake | delicious_rating
--------------------------------------------
1 | Strawberry | Super Delicious
2 | Strawberry | Mouth Heaven
3 | Blueberry | Godly
4 | Blueberry | Super Delicious
I want to find all the cupcakes that have a 'Super Delicious' AND 'Mouth Heaven' rating. I feel like this is easily achievable using a group by
clause and maybe a having
.
I was thinking:
select distinct(cupcake)
from cupcake_rating
group by cupcake
having delicious_rating in ('Super Delicious', 'Mouth Heaven')
I know I can't have two separate AND statements. I was able to achieve my goal using:
select distinct(cupcake)
from cupcake_rating
where cupcake in ( select cupcake
from cupcake_rating
where delicious_rating = 'Super Delicious' )
and cupcake in ( select cupcake
from cupcake_rating
where delicious_rating = 'Mouth Heaven' )
This will not be satisfactory because once I add a third type of rating I am looking for, the query will take hours (there are a lot of cupcake ratings).
© Stack Overflow or respective owner