# Want to elasticsearch sql select the decimal column output more decimal place **URL:** \ **Category:** Elasticsearch\ **Created:** [November 27, 2018, 6:04am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274 "2018-11-27T06:04:29Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [November 27, 2018, 6:04am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/1 "2018-11-27T06:04:29Z")
I am using elastic 6.4, and using /\_xpack/sql to output some columns. Due to use order by command, have to average number columns that output just have 1 decimal place. In my case, I use my local vbox with ubuntu 18.04 and elasticsearch sql can output many decimal place, but I using the redhat machine that it output the average column like 0.0. Can someone help me to get the correct settings? vbox picture like below: ![ES%20SQL%20Vbox](https://us1.discourse-cdn.com/elastic/original/3X/2/9/29cfc1b6ba209a683bb7c8819e0214cefe9085ab.png) redhat machine1 like below: ![ES%20SQL%20NHI](https://us1.discourse-cdn.com/elastic/original/3X/2/7/27d8e1226f78c121faf438f3c72b8185d3591dde.png) update： I have two redhat machine, and one works normally. redhat machine2 like below: ![ES%20SQL%20NHI2](https://us1.discourse-cdn.com/elastic/original/3X/d/0/d08f4cc36f07828a0e8cf9296ede2ffc7a5586ee.png) ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [November 28, 2018, 2:34am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/2 "2018-11-28T02:34:39Z")
Hello. Does my environment of elasticsearch set wrong? Update: Using es query and output result didn't have decimal And another redhat machine has same problem in this morning. ![ES%20query](https://us1.discourse-cdn.com/elastic/original/3X/9/9/99cb159cf2879c50bf260cf8b2eeb02ae7f96add.jpeg) ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [November 30, 2018, 2:18am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/3 "2018-11-30T02:18:54Z")
Any one? ---
**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:** [December 2, 2018, 10:00pm UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/4 "2018-12-02T22:00:27Z")
Hi @zxc654951, I see that every time you get `0.0` you are using index `logstash_t2_2018_11_23`. A fair test between two different machines would be the same query on the same index. But, in your case, you perform the query on two different indices. To me, your tests don't seem relevant for the issue. Please, test the same query on the same index on both machines (the one showing 0.0 and the one showing "good" numbers). ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [December 3, 2018, 6:23am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/5 "2018-12-03T06:23:31Z")
Sorry, I can't got the `logstash_t2_2018_11_23` or `logstash_t2_2018_11_23` because I delete those indices. Therefor, I got "good" numbers with delete all indices and re-logstash data. It query normally now, but I consider deleted that is not a good solution. ![ELK%201%20essql](https://us1.discourse-cdn.com/elastic/original/3X/1/1/113f3aa071beb22e74d43f759df9a5de6280d1d1.png) ![ELK%202%20essql](https://us1.discourse-cdn.com/elastic/original/3X/8/2/821e34ca9493dbeac980110befdc6c81bdd661c3.png) Finally, I don't know what can solve this question. ---
**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:** [December 3, 2018, 6:36am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/6 "2018-12-03T06:36:12Z")
My assumption is that the index you tested on didn't have good data in it. And, thus, you got the `0.0` everywhere. Next time you notice this issue, please test the same query on the same index on multiple machines. Thanks. ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [December 3, 2018, 9:31am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/7 "2018-12-03T09:31:20Z")
Two machine load difference data source but the server's settings are same. Queryed data maybe not like you want. Some problem is the same index in same machine which will query different result with time pass and indices grow. If I get it again then I will update the content. Thanks. ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [December 4, 2018, 6:22am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/8 "2018-12-04T06:22:38Z")
I encounter the problem again. My machine and ELK's conf, yml... are not change. You can see my the results of sql query and discover of kibana below: ![ELK%201%20essql](https://us1.discourse-cdn.com/elastic/original/3X/4/f/4f5986b1d930e00a0d75600d8bdf56f84749a168.png) ![raw%20data%201](https://us1.discourse-cdn.com/elastic/original/3X/4/8/488ef9f23ba2d08b97e91b477fc94c043a89ab4a.png) ![ELK%202%20essql](https://us1.discourse-cdn.com/elastic/original/3X/d/1/d1d1480aea4e11f0c74303de6132440a37ebae4d.png) ![raw%20data%202](https://us1.discourse-cdn.com/elastic/original/3X/b/d/bda107a437b6befacfb538eaab7bf9767411eb91.png) When I change the query index, the results are different for elapsed\_time that one is decimal and another is `0.0`. But in the discover is show the elapsed\_time is good. Because this query problem, I can't average the elapsed\_time. Maybe my English is poor to show my problem, but I hope some people can tell me how to solve this problem. Thanks a lot. ---
**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:** [December 4, 2018, 9:01am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/9 "2018-12-04T09:01:39Z")
What is the result of these two requests? `GET logstash_t2_2018_12_04/_mapping/*/field/elapsed_time` and `GET logstash_t2_2018_12_03/_mapping/*/field/elapsed_time` ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [December 6, 2018, 1:56am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/10 "2018-12-06T01:56:41Z")
I try two request and result as follow: `{ "logstash_t2_2018_12_04": { "mappings": { "doc": { "elapsed_time": { "full_name": "elapsed_time", "mapping": { "elapsed_time": { "type": "long" } } } } } } }` `{ "logstash_t2_2018_12_03": { "mappings": { "doc": { "elapsed_time": { "full_name": "elapsed_time", "mapping": { "elapsed_time": { "type": "float" } } } } } } }` It's seem data type be changed but I don't change any setting. How can I do next step? ---
**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:** [December 6, 2018, 2:12am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/11 "2018-12-06T02:12:47Z")
The data type didn't change. If this is data coming from Logstash and you don't have an index template in which you specifically say that `elapsed_time` should be `float` then Elasticsearch will guess the type of the field from the first document that comes to the index. If it happens that the first value coming in doesn't have decimals, then Elasticsearch will not make that field a `float`. So, I highly suggest defining an index template for these indices so that when a new index gets created every day, it will use that template for its mapping. These are index templates: [https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-templates.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-templates.html). Also, I strongly suggest reading this blog post, as it gives you step-by-step instructions (and explanations) on how to define your own template and why: [https://www.elastic.co/blog/logstash\_lesson\_elasticsearch\_mapping](https://www.elastic.co/blog/logstash_lesson_elasticsearch_mapping) ---
**Author:** ![zxc654951](https://avatars.discourse-cdn.com/v4/letter/z/c89c15/32.png) [@zxc654951](https://discuss.elastic.co/u/zxc654951)\ **Post date:** [December 7, 2018, 1:22am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/12 "2018-12-07T01:22:15Z")
It works normally after I set index template, thanks. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 4, 2019, 1:22am UTC](https://discuss.elastic.co/t/want-to-elasticsearch-sql-select-the-decimal-column-output-more-decimal-place/158274/13 "2019-01-04T01:22:25Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.