FROM and SHOW

FROM picks the schema a query reads. SHOW lists the metrics to return, in the order you want the columns.

FROM <schema>
  SHOW <metric or calculation> [AS <name>], …

FROM

A query reads one schema. Its metrics, dimensions and time axes are the names the rest of the query can use. A metric from another schema is an error, and so is a second schema. To compare numbers from two schemas, write two queries.

FROM payments
  SHOW net_payments
  DURING last_month

SHOW

SHOW lists metrics, separated by commas. They come back in that order, after the dimensions the query groups by.

Bookings and cancellations last quarter

FROM bookings
  SHOW bookings, cancellations, cancellation_rate
  DURING last_quarter

Name a column with AS

AS gives a column your own name. The name follows the same rules as metric names: lowercase, with underscores between words.

FROM bookings
  SHOW cancellation_rate AS cancelled_share
  DURING last_quarter
  GROUP BY channel

A few names are taken. You can't use a dimension the query groups by, a database word such as user or select, a name with two underscores in a row, or is_total, having_1, having_2 and so on, which SERVOS QL uses for columns it adds.

Dimensions in SHOW

A dimension in SHOW must also be in GROUP BY. Grouped dimensions always come back first, so SHOW usually lists only metrics.

FROM rooms
  SHOW room_type, occupancy
  DURING last_month

FROM rooms
  SHOW occupancy
  DURING last_month
  GROUP BY room_type

Calculations

SHOW can do arithmetic with +, -, *, / and parentheses over metrics and numbers. A calculation needs a name with AS.

SERVOS QL computes a calculation for each row from the metrics' own totals, the way it computes a rate such as ADR. It never averages per-row results. The unit follows from the parts. Money divided by nights or guests is money. Money divided by money is a plain number. Division by zero leaves the cell empty.

Revenue per guest night

FROM rooms
  SHOW room_revenue / guest_nights AS revenue_per_guest_night
  DURING last_month
  GROUP BY room_type

Discounts as a share of sales

FROM revenue
  SHOW discounts / gross_sales * 100 AS discount_percent
  DURING last_month
  GROUP BY revenue_category

Numbers next to metrics

A number you add to or take from a metric is in that metric's unit, as you read it. room_revenue - 1000 is a thousand less in your currency, and occupancy + 5 is five percentage points more. A number you multiply or divide by is a plain factor. Dimensions can't take part in arithmetic.

FROM rooms
  SHOW room_revenue - 50000 AS above_target
  DURING last_month
  GROUP BY room_type

Leave out SHOW

A query that names its metrics in VISUALIZE can leave out SHOW. The editor writes those metrics into a SHOW when it formats the query.

FROM rooms
  DURING this_quarter
  TIMESERIES week
  VISUALIZE occupancy, adr TYPE line