Tutorial: Building with CleaveDB

Summaries and Totals

Some requests do not need a document-by-document list. A dashboard may need the number of active users; a manager may want the five highest salaries; an analyst may need the values represented across departments. SCOOP includes summary modes for these common questions, so the query can return a compact answer instead of sending the whole bucket to application code.

Count, distinguish, and compare

THE TALLY returns a document count. ONLY UNIQUE asks for distinct values of a field, such as the departments present in a bucket. THE HIGHEST and THE LOWEST return a requested number of documents ranked by a field, useful for finding top salaries, highest prices, or the least costly options. Conditions such as WHERE can scope the records counted or compared.

SCOOP THE TALLY OF users
SCOOP ONLY UNIQUE department FROM employees
SCOOP THE HIGHEST 5 salary FROM employees
SCOOP THE LOWEST 3 price FROM products

These forms are useful for dashboards and decision points: a count can answer “how many?”, unique values can populate a category list, and ranked results can bring the extremes into view. The result is still a SCOOP response, but its shape reflects the question—count, distinct values, or ranked documents.

Take a slice from either end

THE FIRST and THE LAST request a specified number of documents. They can help retrieve a short sample or a segment of a result. If “first” means newest, oldest, or otherwise meaningful to the application, pair the request with an explicit ordering rule so the intended sequence is clear.

SCOOP THE FIRST 10 FROM logs
SCOOP THE LAST 2 FROM users

A size on its own says how many records you want; ordering gives that size meaning. A timeline, for example, should define its time order before interpreting a “first” or “last” slice as older or newer activity.

Add a field and group the result

THE TOTAL sums a numeric field, and GROUPED BY organizes that sum by another field. This is a concise way to compare totals across regions, departments, or other categories without first retrieving every matching sale into application code. For a dedicated analytics workflow, CleaveDB also documents DISTILL; grouped SCOOP totals are handy when the aggregate belongs beside ordinary document retrieval.

SCOOP THE TOTAL revenue FROM sales GROUPED BY region

Here, revenue is the value being added and region is the category that divides the total. If you are used to SQL, this resembles a grouped aggregate, though it uses CleaveQL’s document and bucket vocabulary. Apply conditions when the total should represent only a particular subset of the data.