[SOLVED] Counting id for both days SQL

Issue

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

Solution

try like below

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

Answered By – Zaynul Abadin Tuhin

Answer Checked By – Candace Johnson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.