How do I best show MAX COUNT values for multiple rows?

user_17906369 0
Viewed 41

First post, so please excuse me if I'm lacking the proper phrasing or information. Happy to add any additional info where I can!

I'm working with two tables:

trips: id, delivery_id, delivery_person_id, start_date, end_date

delivery_people: id, name

I'm trying to get a list of the maximum number of deliveries that each delivery person has made in a single trip, assuming multiple deliveries can be made in one trip. I'm having trouble figuring out the best way to join these and show multiple counted MAX values.

I've been able to pull the Delivery Person ID, Trip ID, and a count of the number of deliveries made per trip with this:

SELECT delivery_person.id AS deliver_person_id, trip.id AS trip_id, COUNT(trips.delivery_id) AS Number_Of_Deliveries
FROM trips
INNER JOIN delivery_people
ON trips.delivery_person_id = delivery_people.id
GROUP BY delivery_people.id, trips.ID
ORDER BY delivery_id;

I've also gotten a 'Max' count of dogs by delivery person id, but the result is clearly wrong (the Max_Number_Of_Dogs columns contains all the same number, which doesn't match my data) using:

SELECT d.id AS delivery_person_id, max(count(t.delivery_id)) OVER () AS Max_Number_Of_Deliveries
FROM trips t
JOIN delivery_people d
ON t.delivery_person_id = d.id
GROUP BY d.id
ORDER BY d.id ASC;

I've been playing with this for a while and reviewing other articles, but just can't seem to figure this one out. Any recommendations?

EDIT: I'm making the assumption that multiple deliveries can be performed in one trip. Adding CREATE tables:

CREATE TABLE "ADMIN"."TRIPS" 
   (    "ID" NUMBER(38,0), 
    "DELIVERY_ID" NUMBER(38,0), 
    "DELIVERY_PERSON_ID" NUMBER(38,0), 
    "START_DATE" TIMESTAMP (6), 
    "END_DATE" TIMESTAMP (6)
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

CREATE TABLE "ADMIN"."DELIVERY_PEOPLE" 
   (    "ID" NUMBER(38,0), 
    "NAME" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP"
   )  DEFAULT COLLATION "USING_NLS_COMP" ;
sql
1 Answers

you shouldnt use max() over() statement. due to no partition value, it produce the same info for each row. so:

SELECT w.id AS walker_id, count(d.DOG_ID) AS Number_Of_Dogs
  FROM dog_walks d
  JOIN walkers w
    ON d.walker_id = w.id
 GROUP BY w.id
 ORDER BY w.id ASC;

Then you can get max of number of dogs.

So second question regarding showing the maximum number of deliveries each delivery person has made in one trip:

SELECT deliver_person_id,
       max(Number_Of_Deliveries_per_trip) as max_Number_Of_Deliveries_per_trip
  FROM (
       SELECT delivery_people.id AS deliver_person_id, 
              COUNT(trips.delivery_id) OVER (PARTITION BY delivery_people.id, trip.id) AS Number_Of_Deliveries_per_trip
         FROM trips
        INNER JOIN delivery_people
           ON trips.delivery_person_id = delivery_people.id
        )
 GROUP BY deliver_person_id
 ORDER BY Number_Of_Deliveries_per_trip desc;
user_176916540
Related