使用UNION进行三次查询

Question

I'm trying to merge three separate queries into one using the UNION function of MYSQL.

I want to output one table of data that contains the booking_count, reserve_count and cancel_count. Some rows will have values for all three, some will only have values for one.

I wonder if anyone could point out where I'm going wrong?

The errors I am getting are:

Notice: Undefined index: cancel_count Notice: Undefined index: reserve_count

I assume this is a problem with the MYSQL query as the PHP looks fine

$query = "SELECT id, class_date, class_id, COUNT(*) AS booked_count   
          FROM bookings
          WHERE booking_status='#BOOKED#'
          UNION ALL

          SELECT id, class_date, class_id, COUNT(*) AS reserve_count  
          FROM bookings
          WHERE booking_status='#RESERVE#'
          UNION ALL

      SELECT id, class_date, class_id, COUNT(*) AS cancel_count  
          FROM bookings
          WHERE booking_status='#CANCELLED#'
          GROUP BY class_date, class_id
          ORDER BY class_date ASC, class_id ASC" ;

$result = mysqli_query($sql,$query);

while($row = mysqli_fetch_assoc($result)) {
   $union[$row['id']] = array('class_date' => $row['class_date'], 'class_id' => $row['class_id'], 'booked_count' => $row['booked_count'], 'reserve_count' => $row['reserve_count'], 'cancel_count' => $row['cancel_count']);
}

echo '<table><tr><th>id</th><th>class_id</th><th>class_date</th><th>booking_number</th><th>reserve_number</th><th>cancelled_number</th></tr>';

foreach($union as $union) {
   echo '<tr>';
   echo '<td>&nbsp;</td>'; // id
   echo '<td>'.$union['class_id'].'</td>';
   echo '<td>'.$union['class_date'].'</td>';
   echo '<td>'.$union['booked_count'].'</td>';
   echo '<td>'.$union['reserve_count'].'</td>';
   echo '<td>'.$union['cancel_count'].'</td>';
   echo '</tr>';
}

echo '</table>';

Answer 1

I think something like this would work:

SELECT id, class_date, class_id,
  SUM(booked_count) AS booked_count,
  SUM(reserve_count) AS reserve_count,
  SUM(cancel_count) AS cancel_count
FROM
(SELECT id, class_date, class_id,
  1 AS booked_count,
  0 AS reserve_count,
  0 AS cancel_count 
FROM bookings
WHERE booking_status = '#BOOKED#'
UNION ALL
SELECT id, class_date, class_id,
  0 AS booked_count,
  1 AS reserve_count,
  0 AS cancel_count 
FROM bookings
WHERE booking_status = '#RESERVE#'
UNION ALL
SELECT id, class_date, class_id,
  0 AS booked_count,
  0 AS reserve_count,
  1 AS cancel_count  
FROM bookings
WHERE booking_status = '#CANCELLED#') AS t
GROUP BY class_date, class_id