Aggregation

Aggregation queries enable the summarization and analysis of large datasets.
Through leveraging these aggregation queries, you can quickly aggregate your records to identify trends, relationships, and anomalies, which provide insights for your decision-making.

You can enable the Aggregation setting for your type, which will automatically generate the aggregate query type: aggregate<type_name>s.
Available operations are count, max, min, sum, avg, totalSize, and groupBy.

Example

<span><span style="color: var(--shiki-color-text)">Payroll: tailordb.#Type </span><span style="color: var(--shiki-token-keyword)">&amp;</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)">  Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">payroll model</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">  TypePermission:  permissions.employee</span></span>
<span><span style="color: var(--shiki-color-text)">  Settings: {</span></span>
<span><span style="color: var(--shiki-color-text)">    </span><span style="color: var(--shiki-token-comment)">// enable Aggregation</span></span>
<span><span style="color: var(--shiki-color-text)">    Aggregation: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)">  }</span></span>
<span><span style="color: var(--shiki-color-text)">  Fields: {</span></span>
<span><span style="color: var(--shiki-color-text)">    name: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:        tailordb.#TypeString</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">name.</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    paidAt: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:        tailordb.#TypeDatetime</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">paidAt</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">      Required:    </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    paidAmount: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:    tailordb.#TypeInt</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">paidAmount</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">      Required:  </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    payrollType: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:        tailordb.#TypeEnum</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">paidType</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">      Required:     </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)">      AllowedValues: [</span></span>
<span><span style="color: var(--shiki-color-text)">        {Value: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">PAID</span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-punctuation)">,</span><span style="color: var(--shiki-color-text)"> Description:   </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">PAID type</span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-color-text)">}</span><span style="color: var(--shiki-token-punctuation)">,</span></span>
<span><span style="color: var(--shiki-color-text)">        {Value: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">UNPAID</span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-punctuation)">,</span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">UNPAID type</span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-color-text)">}</span><span style="color: var(--shiki-token-punctuation)">,</span></span>
<span><span style="color: var(--shiki-color-text)">      ]</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    payrollCode: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:        tailordb.#TypeString</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">payrollCode</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    customerID: {</span></span>
<span><span style="color: var(--shiki-color-text)">      Type:        tailordb.#TypeUUID</span></span>
<span><span style="color: var(--shiki-color-text)">      Description: </span><span style="color: var(--shiki-color-text)">&quot;</span><span style="color: var(--shiki-token-string-expression)">customerID</span><span style="color: var(--shiki-color-text)">&quot;</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">  }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>

Tailor DB auto-generates aggregatePayrolls, allowing you to quickly retrieve summarized results for Payroll.
Based on the payroll type, here's the sample query to aggregate the payroll data:

<span><span style="color: var(--shiki-token-keyword)">query</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)">  aggregatePayrolls {</span></span>
<span><span style="color: var(--shiki-color-text)">    max {</span></span>
<span><span style="color: var(--shiki-color-text)">      paidAmount</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    sum {</span></span>
<span><span style="color: var(--shiki-color-text)">      paidAmount</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    min {</span></span>
<span><span style="color: var(--shiki-color-text)">      paidAmount</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    avg {</span></span>
<span><span style="color: var(--shiki-color-text)">      paidAmount</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    count {</span></span>
<span><span style="color: var(--shiki-color-text)">      name</span></span>
<span><span style="color: var(--shiki-color-text)">      toHour {</span></span>
<span><span style="color: var(--shiki-color-text)">        paidAt</span></span>
<span><span style="color: var(--shiki-color-text)">      }</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">    totalSize</span></span>
<span><span style="color: var(--shiki-color-text)">    groupBy {</span></span>
<span><span style="color: var(--shiki-color-text)">      toDay {</span></span>
<span><span style="color: var(--shiki-color-text)">        paidAt</span></span>
<span><span style="color: var(--shiki-color-text)">      }</span></span>
<span><span style="color: var(--shiki-color-text)">      name</span></span>
<span><span style="color: var(--shiki-color-text)">    }</span></span>
<span><span style="color: var(--shiki-color-text)">  }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>

In the [PayrollsAggregationResult] type, you can specify how you aggregate the data by using groupBy.
Other fields such as count and sum will return results based on the grouping specified in groupBy.
However totalSize always returns the total number of records regardless of the grouping.

The calculation fields count, sum min and max will be applicable only for the Integer and Float types.
Besides, grouping by time windows such as toDay and toHour will be applicable only for the Datetime and Date types.