How to find DeliveryagentID in different cities on same date

Table

 DeliveryagentID, Date,    City,     Cnt_Delivery
'1001', '2021-03-21 00:00:00', 'Bangalore', '45'
'1002', '2022-03-21 00:00:00', 'Delhi',     '35'
'1002', '2023-03-21 00:00:00', 'Delhi',     '70'
'1002', '2024-03-21 00:00:00', 'Chennai',   '45'
'1003', '2021-03-21 00:00:00', 'Mumbai',     '45'
'1003', '2024-03-21 00:00:00', 'Mumbai',     '85'
'1003', '2024-03-21 00:00:00', 'Bangalore',   '50'

enter image description here

Output should be 1003 because its present in two cities on same date.

Did not get exeat output #Q2 Write sql query to find AgentID at diffrent location on same date

select DeliveryagentID,
       Date,
       count(Date) as CountR 
from fraud 
group by Date,DeliveryagentID 
having CountR>1;

CodePudding user response：

Aggregate by agent and date and then assert that two different cities are present:

SELECT DISTINCT DeliveryagentID
FROM fraud
GROUP BY DeliveryagentID, Date
HAVING MIN(City) <> MAX(City);

The above query is fairly instance of where it makes sense to use DISTINCT along with GROUP BY. A given agent might have multiple dates on which it was present in more than one city. However, we only want to report each matching once, which the DISTINCT select handles.

CodePudding user response：

For your case, to find DeliveryagentID in different cities on same date, you can use this query:

select DeliveryagentID,
       Date,
       count(distinct City) as CountR 
from fraud 
group by Date,DeliveryagentID 
having CountR>1;