MySQL SUM CASE returning incorrect calculated amount

Question

Apologies for the confusing title

I have a table 'trainees' which lists booked places on a sailing trip

I then have a berth options table which lists among other things lists whether the berth is booked individually or as a cabin.

I am trying to get the total number of booked berths, however;

• if the voption_pricedIndividually is 1 then the number of places added to the total should be 1.
• If the voption_pricedIndividually is 0 then regardless of the number of 'trainees' booked the number of places added to the total should be the maximum number of people permitted in the cabin. voption_berthsAvailable. So in this case 3.

In the images above, there are: 2x trainees booked in berth 1 and 3x trainees booked in berth 5.

I need a total number of trainees booked, however if they are in a cabin, regardless of the number booked, it needs to return the maximum occupancy.

So in this case we have 3x individual bookings and then 3x more from the maximum occupancy as set in voption_berthsAvailable.

The expected outcome is 6.

After some great help by @Luuk I'm getting closer but the below is multiplying the sum by the number of rows (30 instead of 6)

SELECT voyage_name, voyage_startDate, voyage_id, session_name, 
SUM(test.bookings) AS berthTotals
FROM voyages

LEFT JOIN voyagesessions ON voyagesessions.session_voyageID = voyages.voyage_id
LEFT JOIN trainees ON trainees.trainee_voyageID = voyages.voyage_id
LEFT JOIN vesseloptions ON vesseloptions.voption_id = trainees.trainee_berthID

LEFT JOIN
(SELECT SUM(bookings) as bookings, trainee_voyageID, trainee_sessionID FROM (
SELECT voption_pricedIndividually, trainee_voyageID, trainee_sessionID,
 CASE WHEN voption_pricedIndividually = 0
    THEN
      MIN(CASE WHEN voption_pricedIndividually = 1
          THEN 1
          ELSE voption_berthsAvailable END)
    ELSE
      SUM(CASE WHEN voption_pricedIndividually = 1
          THEN 1
          ELSE voption_berthsAvailable END)
    END as bookings
FROM trainees
LEFT JOIN vesseloptions ON vesseloptions.voption_id = trainees.trainee_berthID
LEFT JOIN voyagesessions ON voyagesessions.session_id = trainees.trainee_sessionID
GROUP BY voption_pricedIndividually, CASE WHEN voption_pricedIndividually <> 0 THEN voption_id END
) x ) as test ON test.trainee_voyageID = voyages.voyage_id

GROUP BY voyage_name, session_name, voyages.voyage_startDate, voyage_id
ORDER BY voyage_startDate, voyage_name

Getting close but now the math is not working.

The query is nearly there but it is returning the correct number but multiplying it by the number of records. Should be 6 not 30

Structures can be found in the DBFIDDLE https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2d14e5048e2667551fc2d9c6d6010166

Answer 1

Ok, so I may have cheeted a little as this isn't in a complete query and I used some PHP but.....

**GetVoaygesBySession**
SELECT * FROM voyagesessions 
LEFT JOIN voyages ON voyages.voyage_id = voyagesessions.session_voyageID 
WHERE session_deleted != 1

**getTraineesByVoyageAndSession**
SELECT * FROM trainees 
LEFT JOIN vesseloptions ON vesseloptions.voption_id = trainees.trainee_berthID 
WHERE trainee_voyageID = :voyage 
AND trainee_sessionID = :session 
AND trainee_status != 6

Then I created multiple foreach loops to run through each result and build an array I can use totally the berth counts as it loops through.

I basically had it add to $i when it was a individual berth on each loop and just overwrite the same number $s with the total in the berth on each loop for the cabins.

    $i = 0;
$c = 0;
$voyageD = array();
$sessionsandvoyages = $this->voyageModel->getVoyagesBySession();
foreach ($sessionsandvoyages as $trip) {

  $trainees = $this->traineeModel->getTraineesByVoyageAndSession($trip->voyage_id, $trip->session_id);
  foreach ($trainees as $trainee) {
    if ($trainee->voption_pricedIndividually == 1) {
      $i++;
    } else {
      $c = $trainee->voption_berthsAvailable;
    }
  }
  $total = $i + $c;
  $voyageD[] = array(
   'voyage_name' => $trip->voyage_name, 
   'voyage_startDate' => $trip->voyage_startDate, 
   'voyage_id' => $trip->voyage_id, 
   'session_name' => $trip->session_name, 
   'berthBookings' => $total
  );

  $total = 0;
  $i = 0;
  $c = 0;
}

I realise this is probably not the most efficient way of doing it but at the moment it is the only way I can get it to work