Canves, SQL can not order my query

Hello,
I wrote the following query in Canves

SELECT (app_code) AS appcodex , COUNT(DISTINCT component_name) AS total, (COUNT(DISTINCT component_name)/(COUNT(iipm.it_executive))100) As p FROM "devops-ucd*" WHERE iipm.l3_it_head = 'name' GROUP BY appcodex ORDER BY (p) DESC
LIMIT 6

I am trying to order the table by percentage, but I keep receiving this error

Cannot order by non-grouped column [p], expected [app_code] or an aggregate function

Here is how my query results look like

I am not sure how to fix this issue, any help would be appreciated?

It makes sense to ask this specific question in the Elasticsearch forum because it's an Elasticsearch SQL problem, but I think in this scenario you can only order by either a grouped column or a single aggregate function.

SELECT (app_code) AS appcodex , COUNT(DISTINCT component_name) AS total, (COUNT(DISTINCT component_name)/(COUNT(iipm.it_executive)) 100) As percentage FROM "devops-ucd *" WHERE iipm.l3_it_head = 'Kazmi, Naim' GROUP BY appcodex ORDER BY COUNT(DISTINCT component_name) DESC
LIMIT 6

would work, but that's probably not helpful in this scenario.

If you have a low (low in maybe < 1000) number of appcodex terms you could move the sorting and limiting operation to the client like this:

essql query="SELECT (app_code) AS appcodex , COUNT(DISTINCT component_name) AS total, (COUNT(DISTINCT component_name)/(COUNT(iipm.it_executive))100) As percentage FROM \"devops-ucd*\" WHERE iipm.l3_it_head = 'Kazmi, Naim' GROUP BY appcodex"
| sort by="percentage" reverse=true 
| head count=6
| table
| render

This will send the complete table to the client and sort there with the sort canvas function and a subsequent head to just take the top 6 values.

Thank you, that is really helpful

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.