Canves, SQL can not order my query

[emad101]

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?

[flash1293 (Joe Reuter)]

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.

[emad101]

@flash1293

| sort by="percentage" reverse=true | head count=6

Thank you, that is really helpful