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 viewProfileassociated with the schemaEmployeeinto a row sequence of this view’s columns.The Operator Function
groupBy()specifiesDepartmentas 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 certainDepartmentand stores this count inDepartmentCount.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 Functionasc()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.