Canves, SQL can not order my query

Elastic Stack Kibana
1

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

Screen Shot 2020-03-26 at 4.40.31 PM
Screen Shot 2020-03-26 at 4.40.31 PM840×732 46.6 KB

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

1 Like
2

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.

3

Thank you, that is really helpful

4

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