With a Grouped Result

We want to see what departments we have and how many employees are in each one.

An Optic query like this one counts how many view rows contain each unique value in a specified column. It returns a row sequence containing a row for each unique value in that column and the count of rows where that column equals that unique value in descending order by that count:

op.fromView('Employee', 'Profile')
  .groupBy('Department', [op.count('DepartmentCount', 'Department')])
  .orderBy(op.desc('DepartmentCount'))
  .offsetLimit(0, 100)
  .result();

We used this query to retrieve a row sequence containing a Department column and a DepartmentCount column for each unique department with rows in descending order by DepartmentCount:

• The Data Accessor Function fromView() pulls data indexed for the view Profile associated with the schema Employee into a row sequence of this view’s columns.

• The Operator Function groupBy() specifies Department as the column to group by and an Aggregate Function to apply to each resulting group.

• The Operator Function count() is one of many Aggregate Functions. It counts the number of rows with a certain Department and stores this count in DepartmentCount.

• The Operator Function orderBy() sorts its input row sequence into the order specified.

• The Auxiliary Function desc() sorts rows in descending order by the specified column's value. The default order is ascending. The Auxiliary Function asc() explicitly sorts in ascending order.

• The Operator Function offsetLimit() restricts results returned. The first parameter specifies the number of results to skip; the second, the number of results to return. So, (0, 100) returns the first 100 results.

• The Executor Function result() executes the query and returns the results as a row sequence.

Here is the 5-row x 2-column result:

{
  "Department": "R&D", 
  "DepartmentCount": 408
}
{
  "Department": "Sales", 
  "DepartmentCount": 206
}
{
  "Department": "Engineering", 
  "DepartmentCount": 183
}
{
  "Department": "Marketing", 
  "DepartmentCount": 174
}
{
  "Department": "Training", 
  "DepartmentCount": 29
}

• This query returned 5 rows, fewer than the 100 we specified in our offsetLimit(). Therefore, there were only 5 different departments.

• Each row represents a unique department.

• The rows are ordered from the largest to the smallest department, as determined by DepartmentCount's value.

• The columns are the ones groupBy() created, displayed in the order they appear in that function.