niscDocsGitHub →
Vex · Query DSL

Aggregates and groupBy

aggregate maps each output name to an aggregate function: count, countDistinct, sum, avg, min or max. groupBy compiles to GROUP BY, which returns one row per group. Example: seats sold and number of bookings per night.

Query
{
  from: ['bookings'],
  fields: [{ field: 'bookings.show_night', as: 'night' }],
  aggregate: {
    seats: { sum: 'bookings.seats' },
    bookings: { count: '*' },
  },
  groupBy: ['bookings.show_night'],
  sort: [{ field: 'bookings.show_night', dir: 'asc' }],
}
Table
CREATE TABLE bookings (
  id text PRIMARY KEY,
  show_night text NOT NULL REFERENCES shows(night),
  member_id text NOT NULL REFERENCES members(id),
  seats integer NOT NULL
)
SQL
SELECT b1.show_night AS "night", SUM(b1.seats) AS "seats", COUNT(*) AS "bookings"
FROM bookings AS b1
GROUP BY b1.show_night
ORDER BY b1.show_night ASC
LIMIT 100
Output matches the expected value

Runs in this page on the installed package. The input and the expected output are defined by the example.