Counting id for both days SQL-CodePudding

Can anyone help me, please?

The task is to find the number of rides taken by drivers who took a ride on both days

create table rides 
(
    ride_id int, 
    driver_id int,
    ride_in_kms int, 
    ride_fare float, 
    ride_date date
);

insert into rides values (1, 1, 3, 4.45, "2016-05-16");
insert into rides values (2, 1, 4, 8.46, "2016-05-16");
insert into rides values (3, 2, 6, 11.9, "2016-05-16");
insert into rides values (4, 3, 3, 6.76, "2016-05-16");
insert into rides values (5, 2, 6, 13.55, "2016-05-16");
insert into rides values (6, 4, 3, 4.91, "2016-05-20");
insert into rides values (7, 1, 7, 16.77, "2016-05-20");
insert into rides values (8, 3, 9, 16.18, "2016-05-20");
insert into rides values (9, 2, 3, 6.07, "2016-05-20");
insert into rides values (10, 4, 4, 6.25, "2016-05-20");

The output:

driver_id  rides
--------------
1  3
2  3
3  2

CodePudding user response：

try like below

select driver_id,count(*) as cnt
from
rides
group by driver_id
having count(distinct ride_date) > 1

CodePudding user response：

select driver_id ,count( ride_id) as rides
from rides group by driver_id having  driver_id in 
(select driver_id from rides
where ride_date ="2016-05-16" and driver_id in
(select driver_id from rides
 where ride_date="2016-05-20")) ;