In the following query I want to order the total amount , how can I ?
[elasticsearch@hadoop1 bin]$ curl -X POST http://hadoop1:9200/_xpack/sql?format=txt -H 'Content-Type: application/json' -d '{"query": "SELECT acct_num,sum(prod_amt) total from purchase group by acct_num order by total"}'
{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:78: Cannot order by non-grouped column [total], expected [acct_num]"}],"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:78: Cannot order by non-grouped column [total], expected [acct_num]"},"status":400}[elasticsearch@hadoop1 bin]$