# Essql COUNT() vs Measure Unique (Canvas Metric)

**URL:** <https://discuss.elastic.co/t/essql-count-vs-measure-unique-canvas-metric/202319>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [October 4, 2019, 9:36am UTC](https://discuss.elastic.co/t/essql-count-vs-measure-unique-canvas-metric/202319 "2019-10-04T09:36:58Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![\_Louw](https://avatars.discourse-cdn.com/v4/letter/_/ee7513/32.png) [@\_Louw](https://discuss.elastic.co/u/_Louw)\
**Post date:** [October 4, 2019, 9:36am UTC](https://discuss.elastic.co/t/essql-count-vs-measure-unique-canvas-metric/202319/1 "2019-10-04T09:36:58Z")

</div>

I am using Kibana Canvas and making a Metric element over how many logins the system has logged. I ran into a big offset in counts.

When using this query in essql:  
SELECT reference\_ as logins FROM "\*canvas"

and in "Display" using Unique on "logins" I get a count of 342.

When using  
SELECT COUNT(DISTINCT reference\_) as logins FROM "\*canvas"

and in "Display" using Value on "logins" I get a count of 1010.

I looked at [this question](https://discuss.elastic.co/t/unique-count-metric-is-inconsistent/33128) and it seems the unique measure is just an approximation.

But why is the difference so big? And am I sure to get the full count by doing it as example 2 in essql?

---

<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:** [October 4, 2019, 8:47pm UTC](https://discuss.elastic.co/t/essql-count-vs-measure-unique-canvas-metric/202319/2 "2019-10-04T20:47:43Z")

</div>

Not 100% about this, but it's possible the 342 you get to be over a limited number of documents: [Canvas Metric Element: Unique count](https://discuss.elastic.co/t/canvas-metric-element-unique-count/166896/2). Also, the documentation seems to mention this default value: [https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#essql\_fn](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#essql_fn).

From ES-SQL point of view, the first SELECT just retrieves some documents. The second one, on other hand, is running a `cardinality` aggregation on `reference_` field and does actually count the unique, non-null terms in that field. I would trust the second query, and would look for clues (like the one I mentioned above) on what kind of count does Canvas calculate on what set of documents.

---

<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:** [November 1, 2019, 8:48pm UTC](https://discuss.elastic.co/t/essql-count-vs-measure-unique-canvas-metric/202319/3 "2019-11-01T20:48:06Z")

</div>

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