How to use COUNT in IF statement

Question

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:

So I need the ids of all users from table who took the bus 3 times.

In your table `id` is the primary key . So that will be unique for all records in your table. You can get all the `ids where travelMethod = 'bus' ` — Damini Suthar, Mar 17 '22 at 23:48
If `id` is the primary key each `id` is in the table exactly once only, suggest you show us some actual sample data and results you are expecting, — Stu, Mar 18 '22 at 00:03
An image of sample data is useless, as it can't be copied and pasted to create a test table. Please post the sample data in the form of DDL and DML that can be copied, pasted and executed. — Ken White, Mar 18 '22 at 00:15

score 1 · Accepted Answer · answered Mar 18 '22 at 00:14

1

A simple aggregate will give you the required IDs:

select Id
from Travel
where travelMethod='Bus'
group by Id
having count(*)=3;

answered Mar 18 '22 at 00:14

Stu

30,392
6
14
33

score 0 · Answer 2 · answered Mar 18 '22 at 00:17

0

Using having count(*)

   select Id  from Travel  where travelMethod='Bus'
        group by Id having count(*) =3 ;

answered Mar 18 '22 at 00:17

Damini Suthar

1,470
2
14
43

score 0 · Answer 3 · answered Mar 18 '22 at 00:19

0

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

answered Mar 18 '22 at 00:19

fadzrinmadu

63
5

How to use COUNT in IF statement

3 Answers3