SELECT  
    months.year,
    months.month,
    -- Payment and Commission Calculation
    COALESCE(ROUND(SUM(CASE 
                         WHEN (
    trips.trip_status IN ('completed', 'cancel')
    OR (
      trips.trip_status != 'p_cancel_pickup' 
      AND trips.is_cancelled = 1
    )
  ) AND trips.trip_pay_status = 'Paid' AND trips.city_id = 14
                              THEN trips.trip_pay_amount
                         ELSE 0 
                      END),0), 0) AS total_amt_paid,

    COALESCE(ROUND(SUM(CASE 
                         WHEN trips.trip_status = 'p_cancel_pickup' 
                              AND trips.is_cancelled = '1' 
                              AND trips.trip_pay_status = 'Paid'
                              AND trips.city_id = 14
                              AND trips.trip_pay_mode != 'null' 
                              THEN trips.trip_pay_amount
                         ELSE 0 
                      END),0), 0) AS total_amt_commission,

    -- Total amount (sum of paid and commission)
    COALESCE(ROUND(SUM(CASE 
                         WHEN (
    trips.trip_status IN ('completed', 'cancel')
    OR (
      trips.trip_status != 'p_cancel_pickup' 
      AND trips.is_cancelled = 1
    )
  ) AND trips.trip_pay_status = 'Paid' AND trips.city_id = 14
                              THEN trips.trip_pay_amount
                         ELSE 0 
                      END),0), 0) +
    COALESCE(ROUND(SUM(CASE 
                         WHEN trips.trip_status = 'p_cancel_pickup' 
                              AND trips.is_cancelled = '1' 
                              AND trips.trip_pay_status = 'Paid'
                              AND trips.city_id = 14
                              AND trips.trip_pay_mode != 'null' 
                              THEN trips.trip_pay_amount
                         ELSE 0 
                      END),0), 0) AS total_amt,

                       COALESCE(ROUND(SUM(CASE 
                       WHEN LOWER(tronsactions.trans_pay_mode) = 'cash' THEN tronsactions.total_amt
                       ELSE 0 
                  END), 0), 0) AS cash_amount,

  COALESCE(ROUND(SUM(CASE 
                       WHEN LOWER(tronsactions.trans_pay_mode) = 'card' THEN tronsactions.total_amt
                       ELSE 0 
                  END), 0), 0) AS card_amount,

  COALESCE(ROUND(SUM(CASE 
                       WHEN LOWER(tronsactions.trans_pay_mode) = 'wallet' THEN tronsactions.total_amt
                       ELSE 0 
                  END), 0), 0) AS wallet_amount,

  COALESCE(ROUND(SUM(tronsactions.total_amt), 0), 0) AS total_payment_amount

FROM 
    (
        SELECT 
            YEAR(CURDATE()) AS year, MONTH(CURDATE()) AS month
        UNION ALL
        SELECT 
            CASE WHEN MONTH(CURDATE()) = 1 THEN YEAR(CURDATE()) - 1 ELSE YEAR(CURDATE()) END AS year, 
            CASE WHEN MONTH(CURDATE()) = 1 THEN 12 ELSE MONTH(CURDATE()) - 1 END AS month
        UNION ALL
        SELECT 
            CASE WHEN MONTH(CURDATE()) IN (1, 2) THEN YEAR(CURDATE()) - 1 ELSE YEAR(CURDATE()) END AS year, 
            CASE WHEN MONTH(CURDATE()) = 1 THEN 11 WHEN MONTH(CURDATE()) = 2 THEN 12 ELSE MONTH(CURDATE()) - 2 END AS month
        UNION ALL
        SELECT 
            CASE WHEN MONTH(CURDATE()) IN (1, 2, 3) THEN YEAR(CURDATE()) - 1 ELSE YEAR(CURDATE()) END AS year, 
            CASE WHEN MONTH(CURDATE()) = 1 THEN 10 WHEN MONTH(CURDATE()) = 2 THEN 11 WHEN MONTH(CURDATE()) = 3 THEN 12 ELSE MONTH(CURDATE()) - 3 END AS month
        UNION ALL
        SELECT 
            CASE WHEN MONTH(CURDATE()) IN (1, 2, 3, 4) THEN YEAR(CURDATE()) - 1 ELSE YEAR(CURDATE()) END AS year, 
            CASE WHEN MONTH(CURDATE()) = 1 THEN 9 WHEN MONTH(CURDATE()) = 2 THEN 10 WHEN MONTH(CURDATE()) = 3 THEN 11 WHEN MONTH(CURDATE()) = 4 THEN 12 ELSE MONTH(CURDATE()) - 4 END AS month
        UNION ALL
        SELECT 
            CASE WHEN MONTH(CURDATE()) IN (1, 2, 3, 4, 5) THEN YEAR(CURDATE()) - 1 ELSE YEAR(CURDATE()) END AS year, 
            CASE WHEN MONTH(CURDATE()) = 1 THEN 8 WHEN MONTH(CURDATE()) = 2 THEN 9 WHEN MONTH(CURDATE()) = 3 THEN 10 WHEN MONTH(CURDATE()) = 4 THEN 11 WHEN MONTH(CURDATE()) = 5 THEN 12 ELSE MONTH(CURDATE()) - 5 END AS month
    ) AS months    
    LEFT JOIN tronsactions
  ON  tronsactions.is_delete = 0
  AND tronsactions.trans_type = 'Trip'
  AND tronsactions.city_id = 14
  AND YEAR(DATE_ADD(DATE_ADD(tronsactions.trans_date, INTERVAL 5.5 HOUR), INTERVAL 00 MINUTE)) = months.year
    AND MONTH(DATE_ADD(DATE_ADD(tronsactions.trans_date, INTERVAL 5.5 HOUR), INTERVAL 00 MINUTE)) = months.month 
LEFT JOIN trips
    ON trips.trip_id = tronsactions.trip_id AND trips.trip_status IN ('completed', 'cancel','p_cancel_pickup')
  GROUP BY 
    months.year, months.month
HAVING
    (months.month != MONTH(CURDATE()) OR COUNT(trips.trip_id) > 0)
ORDER BY 
    months.year ASC, months.month ASC