Gross Revenues Vs Net Revenue by Month and Region

select
A.mon,
A.city,
sum(A.amount) as net_revenue,
sum(B.amount) as Gross_revenue

from zones z

join

(select
sum(p.amount) as amount,
month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
p.state as state,
z.city as city,
r.start_zone_id as rzid,
p.reservation_id as prid
from payments p

left outer join reservations r
on p.reservation_id = r.id

left outer join zones z
on z.id = r.start_zone_id

left outer join members m
on m.id = r.member_id

where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-4-1'
and m.imaginary = 'normal'
and m.state = 'normal'
and r.state IN ('completed','inuse','reserved')
and p.state = 'normal'
and p.paid_type = 'card'

group by mon,city) A
on A.rzid = z.id

left outer join

(select
sum(p.amount) as amount,
month(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as mon,
z.city as city,
r.start_zone_id as rzid,
p.state as state,
p.reservation_id as prid
from payments p

left outer join reservations r
on p.reservation_id = r.id

left outer join zones z
on z.id = r.start_zone_id

left outer join members m
on m.id = r.member_id

where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and CONVERT_TZ(r.start_at, '+00:00', '+8:00') < '2019-4-1'
and m.imaginary = 'normal'
and m.state = 'normal'
and r.state IN ('completed','inuse','reserved')
and p.state = 'normal'

group by mon,city
) B
on B.rzid = z.id

Group by A.mon,A.city