[SOLVED] SphinxQL: How to SUM() custom field with GROUP BY or RANK results by custom field

SphinxQL: How to SUM() custom field with GROUP BY or RANK results by custom field

I have an index with next structure:

+----+-----------+---------+--------------+
| id | entity_id | content | weight_field |
+----+-----------+---------+--------------+
| 1  | 1         | car     | 1.2          |
+----+-----------+---------+--------------+
| 2  | 1         | desert  | 1.45         |
+----+-----------+---------+--------------+
| 3  | 1         | water   | 1.55         |
+----+-----------+---------+--------------+
| 4  | 2         | water   | 1.1          |
+----+-----------+---------+--------------+
| 5  | 2         | desert  | 1.9          |
+----+-----------+---------+--------------+

Could somebody tell me if possible to SUM() field values with grouping? I tried this

SELECT SUM(weight_field) AS sort, entity_id FROM test_index WHERE MATCH ('@content car|desert|water') GROUP BY entity_id ORDER BY sort DESC

But got error:

syntax error, unexpected '(', expecting $end near '()'

I expected to get next result:

+------+-----------+
| sort | entity_id |
+------+-----------+
| 4.2  | 1         |
+------+-----------+
| 3.0  | 2         |
+------+-----------+

The second way good for me: Use custom weight_field (which contain float values as 1.563, 1.02 and etc) to rank results. But I not sure that it possible to do with

OPTION ranker=...

Solution

So, I finnaly find the reason:

In real code, not in example, I named weight_field as weight. So sphinx recognized it as predefined FUNCTION WEIGHT() and throw error which tells that it wants see () after weight.

After fixing it and re-indexing it works.