Question 21848 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find DeliveryagentID in different cities on same date – Mysql

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;
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search