Aggregated fields derived in a database query can’t be used to sort the output as they are not available in the field list for selection in the ‘Sorting’ tab.
An example use case is two tables, customers and invoices, joining them to output ‘total sales’, ‘total debt’ or ‘total overdue debt’ per customer for display to the user. Sorting on ‘total overduedebt’, for example, allows the user to prioritise follow up actions.
An alternative to do this through the front end using Query Manager does not appear to work if the aggregated field is included in the table to be displayed on teh web page. (This was the subject of a thread Sorting on a SUM() column of a Database Paged Query and a PHP code fix was provided but it hasn’t been discussed since). This might be a bug as the front end sorting does not work as expected with aggregated fields.
Last updated: