Wonderful Relations Logo

Get Wonderful Relations

Aggregates

Function: AGGREGATES

In Datatable Context, two queries are required to correctly display the data:

  1. The main query, which retrieves a limited dataset for the current page.
  2. The aggregate query, which computes values such as counts, sums, or averages over the entire dataset.

Since server-side processing is used, not all data is loaded at once, making it impossible to calculate totals across multiple pages on the frontend. Aggregate queries solve this by executing a secondary query that provides aggregated results.

By default, DataTables execute an aggregate query with COUNT(*) to determine the total number of records, which is then displayed in the footer of the DataTable. This ensures that even when pagination is enabled, the full dataset’s size is accurately reflected.

Purpose

Aggregates allow retrieving summarized information from a dataset.

Common use cases include:

  • Counting rows (e.g., total number of records in a table)
  • Summing values (e.g., total hours worked across all employees)
  • Calculating averages (e.g., average order value in a sales report)
  • Concatenating values (e.g., grouping names of assigned team members)

Aggregate queries can also be utilized in templates, enabling the display of summarized data without additional computations in the frontend.

Parameters

Field

The target field to aggregate.

SQL Function

The SQL operation to be performed (e.g., SUM(), COUNT(), AVG()).

Identifier

Unique key used to reference the aggregate query in templates or footers.

On Demand

If enabled, aggregates will be computed only when explicitly requested, rather than preloaded.

Cache

Allows caching of aggregate results for improved performance.

Example: Summing Values

To compute the total number of hours across all records: 

SELECT SUM(total_hours) FROM wp_your_project_table;

This ensures that while pagination limits the displayed rows, the sum of all rows is still available.

Aggregates can be referenced in the footer of a DataTable to provide a quick summary:

<div class="text-right">##sum_total_hours##</div>

This allows displaying totals dynamically without affecting the main query.

Conclusion

Aggregate queries in Wonderful Relations enable efficient handling of summarized data, ensuring that key metrics such as counts, totals, and averages remain accessible even when pagination or limited queries are used. 🚀

Graph View