How to sum an attribute in a sphinx group search?

Question

I need sphinx to sort the results by the sum of an attribute. I need to:

• Group the results by their IDs (done)
• Sort the results by the SUM of one attribute

I can't find a way to sum this attribute. How could I do this?

(Im using sphinx PHP API)

Answer 1

As far as I can tell, this is not possible in the current Sphinx version. The group by functions are designed to return the group by attribute (that is, the distinct attribute to the group of items), the count within that group, and the 'top' item from the group.

Your best bet is to do your summing outside of Sphinx. You could perform a Sphinx search sorting by your group by attribute, and then loop over those items in PHP, summing as you go. This approach may not be terribly efficient, so it might be something that you want to cache periodically to reduce server load.

Answer 2

Echoing the previous answer, it's just not possible to do within a Sphinx query. While the expression syntax allows for some level of calculations in sorting, it doesn't have any aggregation functions. Unless you store that summed value as an attribute (which could be an option - calculate it during indexing), it's going to have to be a separate SQL query per record.

Answer 3

Sphinx supports aggregate functions (sum, avg, min, max) since 0.9.9-rc2 version. Check the documentation from www.sphinxsearch.com / docs / current.html