Group and Aggregate your Records
You can further organize your results table by grouping records by the values in a chosen column. This will create groups in your results table and you can expand or collapse these records.
If applicable (dependent on your column type), it will also display a value in the grouped row that will display your chosen value type, which is an aggregation of the grouped values. You can choose whether it should be a sum, average, minimum, maximum, count or range, for number and date columns.
You can then enable pivot mode, which will provide a summary of your grouped records with your chosen value type for the columns in your results table.
Group Records in your Results Table
You can group records on the values in a chosen column to help view and categorize records.
Click and hold on a column title.
Drag the column into the row above.
It will separate the records into groups in the Group column on the left of the results table.
You can expand on each section to view the records in each group.
Aggregate on Grouped Columns
You can aggregate numerical and date columns with different functions to quickly analyze and summarize grouped data without manual calculations.
For numerical columns such as integers, floats, currency, etc., you can use the following aggregation functions:
- sum: Total of all values in the group.
- avg: Average of all values in the group.
- min: Smallest value in the group.
- max: Largest value in the group.
- count: Number of non-null values in the group.
For date/datetime columns, you can use the following aggregation functions:
- dateMin: Earliest date in the group.
- dateMax: Latest date in the group.
- dateRange: Date span from earliest to latest.
- dateAvg: Average date in the group.
The aggregated value type will be displayed in the group header.
First, drag a column header into the grouping title row.
On the right of the results table, click on Columns.
You can see any grouped columns under the title Row Groups, and all available columns for aggregation beneath the title Values.
Aggregation values will be displayed, if available, in the row beneath the column titles and will display whether it is a sum, avg, min, max or count.
Choose a column in the Values section.
Click on the column and you can choose the aggregation type from the dropdown.
Loome will update the column in the results table.
It will show the new value in the top row and will update each title with the aggregation type.
Pivot Mode
Pivot mode will provide a summary of your grouped records that could either be your selection of a sum, average, minimum, maximum, count or range for number and date columns in your results table.
Click on Columns on the right of the results table.
Click on the Pivot Mode toggle to enable pivot mode.
It will show your selected value type for each column for all visible records.
In the Values section of the right-hand panel, you can click on each column and choose the value type.
For date columns, you can choose from the following:
- dateMin: Earliest date in the group.
- dateMax: Latest date in the group.
- dateRange: Date span from earliest to latest.
- dateAvg: Average date in the group.
For number columns, you can choose from the following:
- sum: Total of all values in the group.
- avg: Average of all values in the group.
- min: Smallest value in the group.
- max: Largest value in the group.
- count: Number of non-null values in the group.
You can also use pivot mode on grouped columns. Click on your selected column and drag it into the Row Groups section.
Any columns that are used to group records will be displayed in the Row Groups section.
After you have made record groups, you can then choose a value type for each column in the Values section.
You can then view your results and even save it as a view.