Home > Enterprise >  How to use COUNT in IF statement
How to use COUNT in IF statement

Time:03-18

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 ids 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:

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

  • Related