WITH

WITH adds a totals row, percent changes or running totals. List several, separated by commas.

WITH TOTALS | PERCENT_CHANGE | CUMULATIVE, …

TOTALS

The totals row comes first, with is_total set. SERVOS QL computes it from all the rows' data, not by adding up the rows. A rate's total is its parts' totals divided, so total occupancy is all nights sold over all nights available, not the average of the room types' occupancy. A count of distinct things, such as orders, counts each order once. An order with lines in two categories counts once in the total, so the total can be less than the sum of the rows.

FROM rooms
  SHOW room_nights_sold, adr, room_revenue
  DURING last_month
  GROUP BY channel
  WITH TOTALS

PERCENT_CHANGE

PERCENT_CHANGE needs COMPARE TO. It adds one column per metric and comparison.

A change shows green when it is good for the hotel, red when it is bad, and grey when neither is better. Fewer cancellations is good, so a fall in cancellation_rate shows green.

FROM revenue
  SHOW net_revenue, orders
  DURING last_month
  GROUP BY revenue_category
  COMPARE TO stly
  WITH TOTALS, PERCENT_CHANGE

CUMULATIVE

CUMULATIVE turns each period's value into the total so far, along TIMESERIES. CUMULATIVE_VALUES is another name for it. It needs TIMESERIES by day, week, month, quarter or year, and metrics that add up over time. Rates such as occupancy and ADR don't, and nor do distinct counts such as orders or customers. Each schema page marks the metrics that don't.

Month-to-date room revenue against last year

FROM rooms
  SHOW room_revenue
  DURING this_month
  TIMESERIES day
  COMPARE TO stly
  WITH CUMULATIVE, PERCENT_CHANGE

FROM rooms
  SHOW adr
  DURING this_month
  TIMESERIES day
  WITH CUMULATIVE