# Index Search Percentege (\_sql or \_search)

**URL:** <https://discuss.elastic.co/t/index-search-percentege-sql-or-search/268546>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 27, 2021, 12:11am UTC](https://discuss.elastic.co/t/index-search-percentege-sql-or-search/268546 "2021-03-27T00:11:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thiago\_Paiva](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thiago_paiva/32/52326_2.png) [@Thiago\_Paiva](https://discuss.elastic.co/u/Thiago_Paiva)\
**Post date:** [March 27, 2021, 12:11am UTC](https://discuss.elastic.co/t/index-search-percentege-sql-or-search/268546/1 "2021-03-27T00:11:50Z")

</div>

Hi Everyone,

I'd like to get the total percentage of \_doc for each Server in a \_search or \_sql API request.

In both options I can get total docs for the server and the total of all docs, Now I need a way to do calculate the percentage. But I don't know how, Following my Requests:

The sql solution will be the best solution for my necessity. But I can use the \_search too.

Calc that I can't to do:

(100 \* doc\_count\_server / Total\_doc)

**\_Search Request:**

```auto
  "query": {
    "range": {
      "@timestamp": {
				"time_zone": "-03:00",        
        "gte": "now-15m", 
        "lte": "now"                  
      }
    }
  },
	"size": 0,
  "aggs": {
    "Total_doc": {
      "value_count": {
        "field": "S_ComputerName"
      }
  	},
		"total_per_server": {
              "terms": {
                "field": "S_ComputerName"
             }
        }
     }

```

Result:

```auto
{
     "took": 159,
     "timed_out": false,
     "_shards": {
       "total": 4,
       "successful": 4,
       "skipped": 0,
       "failed": 0
     },
     "hits": {
       "total": {
         "value": 10000,
         "relation": "gte"
       },
       "max_score": null,
       "hits": []
     },
     "aggregations": {
       "Total_doc": {
         "value": 30976
       },
       "total_per_server": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
           {
             "key": "SERPW32B",
             "doc_count": 15542
           },
           {
             "key": "SERPW32C",
             "doc_count": 15413
           },
           {
             "key": "SERPW32A",
             "doc_count": 21
           }
         ]
       }
     }
   }

```

**\_SQL request:**

`{"query": "select \"S_ComputerName\" , count(1) FROM \"INDEX*\" a WHERE \"@timestamp\" > CURRENT_TIMESTAMP() - INTERVAL 30 MINUTES and \"CS_URI_Stem\" LIKE '%start.swe' group by \"S_ComputerName\" "}`

Result:

```auto
{
      "columns": [
        {
          "name": "S_ComputerName",
          "type": "keyword"
        },
        {
          "name": "count(1)",
          "type": "long"
        }
      ],
      "rows": [
        [
          "SERPW32A",
          44
        ],
        [
          "SERPW32B",
          32156
        ],
        [
          "SERPW32C",
          32248
        ]
      ]
    }

```

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [March 29, 2021, 9:52am UTC](https://discuss.elastic.co/t/index-search-percentege-sql-or-search/268546/2 "2021-03-29T09:52:33Z")

</div>

If I understand correctly, and since you want the GROUP BY, you'd need to use subselects, to also have the count(\*) and then do the calculation in one query. I'm afraid that this is not possible currently as the support for [subselects is quite limited](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select).

You'd have to execute 2 queries and do the calculation on the client side.

---

<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 26, 2021, 9:52am UTC](https://discuss.elastic.co/t/index-search-percentege-sql-or-search/268546/3 "2021-04-26T09:52:51Z")

</div>

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