mysqlgroup-bywhere-clausedistinct

SQL group by multiple columns with seperate where statements and left join


I want to select all delivery stops for a driver. If 2 orders have the same delivery_id location it should only be one stop. Similarly if the pickup_id and delivery_id of different orders are the same it should also only be one stop. If an order has been 'Picked-up' there should be no pickup stop for it. As a seperate query afterwards I will select the orders to pick-up or deliver to the stop based on looping outcome and using the address_id.

orders table:

order_id pickup_id delivery_id driver_id status o_pickup o_delivery
1 4 2 1 Assigned 3 2
2 4 1 1 Assigned 3 1
3 2 5 1 Assigned 2 0
4 3 5 1 Picked-up 4 0

addresses table:

address_id street lat lat
1 1 St 40.332 -70.332
2 9 Av 40.942 -70.942
3 4 St 41.432 -70.432
4 9 St 41.321 -70.047
5 0 Av 43.031 -72.321

Desired outcome:

address_id street lat lat
4 9 St 41.321 -70.047
2 9 Av 40.942 -70.942
1 1 St 40.332 -70.332
5 0 Av 43.031 -72.321

The following query select the correct distinct pickup_id or delivery_id and organizes in the right order however doesn't combine stop with the same pickup_id and delivery_id, how would I select distinct pickup_id and delivery_id while taking into account order status?

SELECT a.pickup_id as address_id, b.street, b.lat, b.lon, a.o_pickup as o_order 
FROM orders a 
LEFT JOIN addresses b ON a.pickup_id = b.address_id 
WHERE a.driver_id = 1 
AND a.status = 'Assigned' GROUP BY a.pickup_id 
UNION 
SELECT a.delivery_id as address_id, b.street, b.lat, b.lon, a.o_delivery AS o_order 
FROM orders a 
LEFT JOIN addresses b ON a.delivery_id = b.address_id 
WHERE a.driver_id = 1 
AND (a.status = 'Assigned' OR a.status = 'Picked-up') 
GROUP BY a.delivery_id 
ORDER BY o_order DESC;

Lastly I will have to count the to do and completed number of stops. Would I be able to achieve with a count case query?


Solution

  • Use DISTINCT instead of GROUP BY. Otherwise your query was ok. You should only use GROUP BY with aggregate functions (sum, min, max, count...).

    SELECT distinct a.pickup_id as address_id, b.street, b.lat, b.lon, a.o_pickup as o_order 
    FROM orders a 
    LEFT JOIN addresses b ON a.pickup_id = b.address_id 
    WHERE a.driver_id = 1 
    AND a.status = 'Assigned'
    UNION
    SELECT distinct a.delivery_id as address_id, b.street, b.lat, b.lon, a.o_delivery AS o_order 
    FROM orders a 
    LEFT JOIN addresses b ON a.delivery_id = b.address_id 
    WHERE a.driver_id = 1 
    AND (a.status = 'Assigned' OR a.status = 'Picked-up')
    ORDER BY o_order DESC;
    

    See dbfiddle.