# Aggregation inside Visualization
**URL:** https://discuss.elastic.co/t/aggregation-inside-visualization/195033
**Category:** Kibana
**Created:** [August 13, 2019, 1:29pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033 "2019-08-13T13:29:54Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@kamfart](https://discuss.elastic.co/u/kamfart)
#### Post date: [August 13, 2019, 1:29pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/1 "2019-08-13T13:29:54Z")
Hello there,
I try to aggregate data with Kibana visualization but I've got some troubles.
First, some information :
My source file look like that :
ID | name | char | number1 | number2 | number3 | number4 | time
I've parsed that log to extract the information in dedicated field.
Then, I try to sum the "number column" and to group them by ID
I used the DevTools to try, here my Query in SQL (yes I didnt master the DSL yet...) :
POST \_sql?format=txt
{
"query":"SELECT id, (nbDigit + nbSpec + nbUpper + nbLower) as length FROM "test-report-audit\*" WHERE note!='A' AND note!='D'"
}
The output look like :
id | length
---------------+---------------
559980310 |6
404312591 |9
941595003 |10
157634821 |9
977421435 |10
Now, I tried to use this result inside Kibana Verticale Bar to count the number of occurence of each number from the field "length". But I'm stuck.
On this forum, I found (in an another thread) that is not possible to use DSL Query inside vizu.
So I guess I have to left my SQL Query to figure it out with split slices/chart only ?
But, I don't know how to "convert" this result in a Kibana Visualization and moreover I don't know if Kibana can do this kind of operation ?
Thanks for your help,
Kamfart
---
### Author:  [@wylie](https://discuss.elastic.co/u/wylie)
#### Post date: [August 13, 2019, 1:59pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/2 "2019-08-13T13:59:32Z")
Your options here are:
- Create a scripted field in your index pattern which can be used generally in Kibana, especially in Visualize. I think this is your best option.
[https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-guide.html](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-guide.html)
- Use the SQL statement you described while building a Canvas dashboard and visualization
- Use the Vega visualization, which supports querying elasticsearch directly via DSL or SQL
---
### Author:  [@kamfart](https://discuss.elastic.co/u/kamfart)
#### Post date: [August 14, 2019, 3:44pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/3 "2019-08-14T15:44:28Z")
Hi Wylie, thanks for your help !
I've tried the 2nd solution, with Canvas, and it work perfectly 👌 !
Now, I'm trying to understand how Painless work.
But I've got a little problem.
Because is only about Painless langage, do I need to open another thread on Elasticsearch forum ?
Thanks,
Kamfart
---
### Author:  [@wylie](https://discuss.elastic.co/u/wylie)
#### Post date: [August 14, 2019, 3:56pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/4 "2019-08-14T15:56:50Z")
Yes, I think you will get the best help there. I'm definitely not as familiar with that language!
---
### Author:  [@kamfart](https://discuss.elastic.co/u/kamfart)
#### Post date: [August 19, 2019, 1:40pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/5 "2019-08-19T13:40:36Z")
Thanks for all the solution Wylie, I'm going to close this topic.
Kamfart
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 16, 2019, 1:40pm UTC](https://discuss.elastic.co/t/aggregation-inside-visualization/195033/6 "2019-09-16T13:40:39Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.