HAVING

HAVING keeps only the rows whose metrics pass a condition. It runs after the metrics are computed, so it filters groups, such as room types with an ADR above 1 500. To filter the data before it is added up, use WHERE.

HAVING <metric or calculation> <operator> <number> [AND | OR …]

HAVING takes metrics, and calculations by the name you gave them with AS. It compares them with numbers using =, !=, <, <=, >, >=, IN (…) and BETWEEN … AND …, combined with AND, OR, NOT and parentheses.

Numbers are in the unit you read: money in whole currency units, percentages in percent. HAVING occupancy > 80 means above 80%, not above 0.8.

Room types with an ADR between 800 and 2 000

FROM rooms
  SHOW room_revenue, adr
  DURING last_month
  GROUP BY room_type
  HAVING adr BETWEEN 800 AND 2000

Products that sold at least ten

FROM revenue
  SHOW units_sold, net_revenue
  DURING last_month
  GROUP BY product
  HAVING units_sold >= 10

Filter on a metric you don't show

A metric in HAVING doesn't need to be in SHOW. SERVOS QL computes it to filter, then leaves it out of the result.

FROM rooms
  SHOW room_revenue
  DURING last_month
  GROUP BY room_type
  HAVING occupancy > 50 AND adr >= 1000

Filter on a calculation

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

Dimensions go in WHERE

HAVING only takes metrics. A condition on a dimension goes in WHERE.

FROM rooms
  SHOW adr
  DURING last_month
  GROUP BY room_type
  HAVING room_type = 'Suite'