# SQL - Group values that begin with the same letters with horizontal bar chart in Canvas?

**URL:** <https://discuss.elastic.co/t/sql-group-values-that-begin-with-the-same-letters-with-horizontal-bar-chart-in-canvas/171263>\
**Category:** Kibana\
**Tags:** elastic-stack-sql, canvas\
**Created:** [March 7, 2019, 9:40am UTC](https://discuss.elastic.co/t/sql-group-values-that-begin-with-the-same-letters-with-horizontal-bar-chart-in-canvas/171263 "2019-03-07T09:40:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![lobotomeh](https://avatars.discourse-cdn.com/v4/letter/l/96bed5/32.png) [@lobotomeh](https://discuss.elastic.co/u/lobotomeh)\
**Post date:** [March 7, 2019, 9:40am UTC](https://discuss.elastic.co/t/sql-group-values-that-begin-with-the-same-letters-with-horizontal-bar-chart-in-canvas/171263/1 "2019-03-07T09:40:39Z")

</div>

Hello everyone,  
I begin to work with the _Canvas_ section in _Kibana_  
What I try to do is to retrieve the count of several values ; and I need to group certain values together - _the ones that start with the same letters_.  
I want to represent the result of the query in a **Horizontal Bar Chart.**

**My SQL query looks like this :**

```
SELECT 
(SELECT COUNT(*) FROM logs WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM logs WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM logs WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM logs WHERE status='anotherExactErrorValue')

```

When I test this query, [using SQL and a little database, it works](https://www.jdoodle.com/embed/v0/12T5)

**This is my elasticsearch SQL query :**

```
SELECT 
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status LIKE 'missingValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*"
WHERE status LIKE 'errorValue%'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='exactErrorValue'),
(SELECT COUNT(*) FROM "monitoring-func-*" 
WHERE status='anotherExactErrorValue')

```

And I get this error :

```
{
  "error": {
"message": "[essql] > Unexpected error from Elasticsearch: [unresolved_exception] Invalid call to nullable on an unresolved object ScalarSubquery[With[{}]
\\_Project[[?COUNT(?*)]]
\\_Filter[(status) REGEX (LikePattern)#5139]
 \\_UnresolvedRelation[[][index=monitoring-func-*],null,Unknown index [monitoring-func-*]],5142] AS ?"
  }
}

```

Seeing _"unknown Index"_ , I first thought that the wildcard was the problem.

But it's not, it's perfectly fine in my others Elasticsearch queries.  
And I don't see what the null object is.

Is there something about the _Subqueries_ , _the multiple SELECT_ , that Elasticsearch SQL doesn't handle well ? I didn't find any ressource or topics on this, but maybe I've searched the wrong way.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [March 7, 2019, 2:39pm UTC](https://discuss.elastic.co/t/sql-group-values-that-begin-with-the-same-letters-with-horizontal-bar-chart-in-canvas/171263/2 "2019-03-07T14:39:34Z")

</div>

@lobotomeh the sub-selects in ES-SQL work only partially. They are listed as a limitation [here](https://www.elastic.co/guide/en/elasticsearch/reference/6.7/sql-limitations.html#_using_a_sub_select). And the things that don't work with sub-selects is probably a open-ended list there. I'm pretty sure the error you get is related to expanding the wildcard inside the index name from the sub-select.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 4, 2019, 2:39pm UTC](https://discuss.elastic.co/t/sql-group-values-that-begin-with-the-same-letters-with-horizontal-bar-chart-in-canvas/171263/3 "2019-04-04T14:39:40Z")

</div>

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