## What are the query builder actions?

URL: https://docs.atlan.com/product/capabilities/insights/concepts/what-are-the-query-builder-actions

> Learn about what are the query builder actions?.

When using the [Visual Query Builder](https://docs.atlan.com/llms/catalog/insights/query-data/llms.txt) you can use any of the following actions in your query.

To illustrate their use, imagine the following sample tables:

## Data for examples

### Expenses table

| CATEGORY\_ID | MEDIUM | SPEND |
| --- | --- | --- |
| 1 | Facebook | 447000 |
| 1 | Google | 94500 |
| 1 | LinkedIn | 12300 |
| 2 | Sales | 700000 |
| 2 | Marketing | 400000 |
| 3 | Salesforce | 250000 |
| 3 | Hubspot | 75000 |

### Category table

| ID | NAME |
| --- | --- |
| 1 | Advertisement |
| 2 | Salary |
| 3 | Software |
| 4 | Other |

## Actions

### Join data

Join data to combine the contents of several assets. To join data you'll need to select:

- The type of join (see table below).
- The left asset and the column data to match with the right asset's column.
- The right asset and the column data to match with the left asset's column.

| Type of join | Explanation |
| --- | --- |
| [Inner Join](https://dataschool.com/how-to-teach-people-sql/inner-join-animated/) | returns only matching records from both the left table and the right table. |
| [Left Join](https://dataschool.com/how-to-teach-people-sql/left-right-join-animated/#left-join) | returns all records from the left table, and only matching records from the right table. |
| [Right Join](https://dataschool.com/how-to-teach-people-sql/left-right-join-animated/#right-join) | returns all records from the right table, and only matching records from the left table. |
| [Outer Join](https://dataschool.com/how-to-teach-people-sql/full-outer-join-animated/) | returns all records from both tables. |

For example, to get a set of results that shows the category of expenses along with the spend:

- Select `Inner Join` or `Left Join` as the join type
- Select `Expenses` for the left table, and `CATEGORY_ID` as its column
- Select `Category` for the right table, and `ID` as its column

The resulting table would be:

| CATEGORY\_ID | MEDIUM | SPEND | ID | NAME |
| --- | --- | --- | --- | --- |
| 1 | Facebook | 447000 | 1 | Advertisement |
| 1 | Google | 94500 | 1 | Advertisement |
| 1 | LinkedIn | 12300 | 1 | Advertisement |
| 2 | Sales | 700000 | 2 | Salary |
| 2 | Marketing | 400000 | 2 | Salary |
| 3 | Salesforce | 250000 | 3 | Software |
| 3 | Hubspot | 75000 | 3 | Software |

:::info **Did you know?** 
 You can join more than 2 assets by clicking the **Add another** link at the bottom of the _Join data_ tile. Each additional join will add objects to the list of assets that can be used on the left of the join.

:::

### Filter

Filter data to return only some of the rows. To filter data you'll need to select:

- The column data by which to narrow the rows.
- The operation used to compare that column's data.
- The value to compare against.

For example, to retrieve only the advertising spend:

- Select `NAME` for the column
- Select `Equal to` for the operation
- Enter `Advertisement` for the value

The resulting table would be:

| CATEGORY\_ID | MEDIUM | SPEND | ID | NAME |
| --- | --- | --- | --- | --- |
| 1 | Facebook | 447000 | 1 | Advertisement |
| 1 | Google | 94500 | 1 | Advertisement |
| 1 | LinkedIn | 12300 | 1 | Advertisement |

### Group

Group data to combine rows together into buckets. To group data you'll need to select the column by which to bucket the data.

For example, to bucket the data by category:

- Select `NAME`

If you remove the filter step above, the resulting table would be:

| NAME |
| --- |
| Advertisement |
| Salary |
| Software |

:::info **Did you know?** 
 It may look like you've over-simplified the data now. If your query stopped here, that would be true. But think of this action as preparation for other actions, like aggregating.

:::

### Aggregate

Aggregate data to calculate a metric from many rows of data. For example, to calculate a metric against each of the buckets created by grouping. To aggregate data you'll need to select:

- The column on which to run the calculation
- The calculation to run:
 - `Count`: calculate the total number of rows
 - `Unique Count`: calculate the number of unique values for this column across the rows
 - `Sum`: add all the values of the rows together
 - `Max`: find the highest value in the rows
 - `Min`: find the lowest value in the rows
 - `Average`: add all the values of the rows together, then divide by the number of rows

For example, to calculate the total spending by category:

- Select `SPEND` for the column
- Select `Sum` for the calculation

The resulting table would be:

| NAME | sum\_SPEND |
| --- | --- |
| Advertisement | 862800 |
| Salary | 1100000 |
| Software | 325000 |

### Sort

Sort data to return the rows in a defined order. To sort data you'll need to select:

- The column by which to order the results
- The direction in which to order them:
 - `ASC`: return the lowest value as the first row and highest value last
 - `DESC`: return the highest value as the first row and lowest value last

For example, to return the category with the highest spending:

- Select `sum_SPEND` for the column
- Select `DESC` for the order

The resulting table would be:

| NAME | sum\_SPEND |
| --- | --- |
| Salary | 1100000 |
| Advertisement | 862800 |
| Software | 325000 |

---