I have a table like so:
Travel(id, title, date, travelMethod)
with id
as the primary key.
I need to get a list of all id
s that have travelled using the bus 3 times. That's to say, if they rode the bus three times, they would be in this table 3 times.
I think it's something like
SELECT id FROM Travel WHERE (something here with count(*))
but I'm not really sure how to do this, I am new at SQL. Any help would be appreciated.
EDIT: additional info.
I said id
is unique, but that is false for this table. Here is some sample data:
So I need the ids
of all users from table who took the bus 3 times.
CodePudding user response:
A simple aggregate will give you the required IDs:
select Id
from Travel
where travelMethod='Bus'
group by Id
having count(*)=3;
CodePudding user response:
Using having count(*)
select Id from Travel where travelMethod='Bus'
group by Id having count(*) =3 ;
CodePudding user response:
you can try like this
SELECT id FROM Travel GROUP BY id HAVING (something here with count(*))
reference : MYSQL using count in the where clouse