# Sub-queries in KQL Kibana
**URL:** https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505
**Category:** Kibana
**Tags:** kql-kibana-query-language
**Created:** [February 6, 2021, 5:12pm UTC](https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505 "2021-02-06T17:12:29Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Montek\_Singh](https://discuss.elastic.co/u/Montek_Singh)
#### Post date: [February 6, 2021, 5:12pm UTC](https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505/1 "2021-02-06T17:12:30Z")
I am using ELK stack to perform some analysis on my data. My index pattern has 2 types of data
1. The ID of the product clicked
2. The ID of the product sold
I would like to first find all the IDs sold out and based on that find the number of times that ID was clicked.
Example-
1, clicked
1, sold
2, clicked
2, clicked
2, sold
3. clicked
So in this data-\> ID1 and ID2 were sold. And ID1 was clicked once and ID2 was clicked twice.
I am unable to achieve this in KQL.
---
### Author:  [@Marius\_Dragomir](https://discuss.elastic.co/u/Marius_Dragomir)
#### Post date: [February 9, 2021, 4:48pm UTC](https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505/2 "2021-02-09T16:48:11Z")
You can achieve this by using filter aggregations in a visualization.
Either in Lens or even in normal Bar Chart.
You do a terms Aggregation on the product ID, with a count metric. Then you can split that aggregation with a filter one where the first filter is "action: clicked" and the second one is "action: sold".
---
### Author:  [@Montek\_Singh](https://discuss.elastic.co/u/Montek_Singh)
#### Post date: [February 9, 2021, 11:29pm UTC](https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505/3 "2021-02-09T23:29:51Z")
> [@Montek\_Singh](#):
>
> IDs sold out a
This will give me 2 Aggregations one for 'sold' and the other one for 'clicked'.
I would like to get the Number of clicks for all the Sold Items only. When I apply a filter on 'sold', I get only sold rows. Now how can I get the data for the click? Its similar to sub-queries in SQL.
I checked many posts, and all say that Sub-Queries in ES is not possible. Moreover, if I start using "Canvas" and "SQL", it does not support all the Sub-Queries.
As of now, it seems like I will have to store all the data on a database (MySql or MongoDB) and use Grafana for Visualization.....
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 9, 2021, 11:30pm UTC](https://discuss.elastic.co/t/sub-queries-in-kql-kibana/263505/4 "2021-03-09T23:30:26Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.