new column added please modify test cases

refund_item_VS_make_order_free.sql

SELECT SQL_CACHE
  `payment`.`id`                                                                                    AS id,
  `order_item`.`order_id`                                                                           AS order_id,
  GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
    THEN ' '
                         ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer,
  event.name                                                                                        AS event,
  COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (`order_item`.status != -1 AND `order_item`.status >= -2 AND
                                                        `payment`.payment_type_id != 8 and payment.make_order_free = 1))
    THEN `order_item`.id
                 ELSE NULL END)                                                                     AS qty,
  payment.currency                                                                                  AS `currency`,

  CASE WHEN
    payment.make_order_free = 1
  THEN
      ROUND(payment.total + COALESCE(refunds_total, 0), 2)
  ELSE
    ROUND(payment.total , 2)
  END
                                                                                                    AS 'total',


  `payment_type`.`name`                                                                             AS payment_type,
  payment_status.name                                                                               AS status,
  `payment_status`.`id`                                                                             AS status_id,
  DATE_FORMAT(CONVERT_TZ(payment.`paid_on`, '+0:00', '-9:00'), '%d/%m/%Y %H:%i')                    AS 'created',
  `user`.`name`                                                                                     AS 'agent',
  event.id                                                                                          AS event_id,
  payment.checked,
  DATE_FORMAT(CONVERT_TZ(payment.checked_date, '+0:00', '-9:00'), '%d/%m/%Y'),
  DATE_FORMAT(CONVERT_TZ(`payment`.`complete_date`, '+0:00', '-9:00'), '%d/%m/%Y %H:%i')            AS `complete date`,
  `payment`.`delivery_status`                                                                       AS `delivered`
FROM `order_item`
  INNER JOIN `payment` ON payment.id = `order_item`.`payment_id` AND payment.status > 0.0
  LEFT JOIN (SELECT
               sum(`payment_refund`.total) AS `refunds_total`,
               payment_refunds.payment_id  AS `payment_id`
             FROM payment
               INNER JOIN `payment_refunds` ON payment_refunds.payment_id = payment.id
               INNER JOIN `payment` AS `payment_refund` ON `payment_refund`.id = `payment_refunds`.payment_id_refund
             GROUP BY `payment_refunds`.payment_id) AS `refunds` ON `refunds`.payment_id = payment.id
  INNER JOIN event_date_product ON event_date_product.id = order_item.event_date_product_id
  INNER JOIN event_date ON event_date.id = event_date_product.event_date_id
  INNER JOIN event ON event.id = event_date.event_id
  INNER JOIN payment_status ON payment_status.id = payment.status
  INNER JOIN payment_type ON payment_type.id = payment.payment_type_id
  LEFT JOIN user ON user.id = payment.completed_by
  LEFT JOIN submission_entry ON submission_entry.form_submission_id = `payment`.`form_submission_id`
  LEFT JOIN question ON question.id = submission_entry.question_id AND question.var IN ('name', 'email')
WHERE 1 = 1 AND (order_item.status > 0.0 OR order_item.status = -2.0) AND event.id = 4430
      AND question.var IN ('name', 'email')
GROUP BY `order_item`.`order_id`
HAVING 1 = 1
ORDER BY `order_item`.`order_id` DESC