Compare the sums of 2 separate columns with an SQL Statement

I need help with the following code:

SELECT ID, (CASE WHEN SUM(ArrivalsDelay) > SUM(DeparturesDelay) THEN ArrivalsDelay WHEN SUM(ArrivalsDelay) < SUM(DeparturesDelay) THEN DeparturesDelay END) AS ShippingDelay
FROM Shipments 
GROUP BY ID, ArrivalsDelay, DeparturesDelay
ORDER BY ShippingDelay DESC
LIMIT 10;

Issue with the code:

The resultant table contains only NULL in the delay fields. I’m unsure why this happens, as removing ArrivalsDelay and DeparturesDelay from the GROUP BY results in the SELECT failing, as I’ve not included ArrivalsDelay in the GROUP BY which isn’t legal. I’m also not entirely sure the implementation of the procedure I’m trying to complete is correct.

Example of undesired behaviour:

Records:

Prints:

1, null

2, null

The desired behaviour:

Fetch 10 Unique Shipping IDs that have the largest number of Shipping Delays, and the number of Shipping Delays they’ve had (In descending order of Shipping Delays).

What I’m having trouble with:

Configuring the Shipping Delay variable. To clarify, “Shipping Delays”, as stated in the initial description, is not an explicit column. Instead, available are the columns Departure Delays and Arrival Delays (separate columns). I want to compare the sum of Departure Delays with the sum of Arrival Delays and use whichever is larger as the Shipping Delay, for any given Shipping ID.

Further:

But I’m not sure what syntax is actually available to perform the comparison between 2 columns’ sums and select 1 to be assigned to a variable for further use.

So I suppose some pseudocode would be:

Delay = SUM(ArrivalDelay) IF SUM(ArrivalDelay) > SUM(DepartureDelay)
OR
Delay = SUM(DepartureDelay) IF SUM(ArrivalDelay) < SUM(DepartureDelay)

Thanks for any help.

Answer

Based on your comment just now…

SELECT
  ID,
  GREATEST(SUM(ArrivalsDelay), SUM(DeparturesDelay))   AS ShippingDelay
FROM
  Shipments 
GROUP BY
  ID
ORDER BY
  ShippingDelay DESC
LIMIT
  10

Advertisement

Answer