Start a Query

Queries are the cornerstone for creating charts. This is where you’ll pull the data you want to show. All charts must start with an initial query; to help you remember this, all new charts have the first Query Action added and expanded for your convenience!

Choose a data source

Before you can start building your query, you need to decide from where to query the data. Choose one of your connected data sources by clicking the Data Source dropdown from the top-right corner.

Don’t have any data sources listed? Let’s fix that! Check out our data sources documentation for how to add some.

Select a data source from the dropdown

Choose your preferred mode

Now you can actually query data! Queries have two modes:

  • Visual Mode - Quickly add columns and filters and let us generate the SQL query for you
  • SQL Mode - Write out your own SQL query from scratch

Use either mode (or both modes!) as a method to pull data from your selected data source.

Execute your query

When you feel like your query is ready, click Run Query to execute it. The Result Table and Chart Preview will update with your query results.

Keyboard shortcuts! You can also use Cmd + Enter (for Mac) or Ctrl + Enter to run your query.

The executed query depends on which mode is currently active. If you’re using Visual Mode, the auto-generated SQL query is executed; if you’re using SQL Mode, your custom query is executed.

Note: Making changes and executing the query in Visual Mode updates the query in SQL Mode, but this isn’t true for the reverse – making changes and executing the query in SQL Mode doesn’t affect what’s shown in Visual Mode. If you’re switching between Visual Mode and SQL Mode, your changes to Visual Mode don’t impact your returned results until you re-execute your query from Visual Mode. Also be aware re-executing the query in Visual Mode may override changes you’ve made in SQL Mode. We understand this might be a bit confusing. Maybe our FAQ on editing in both modes will shed more light on the subject.

After running your first query, you can start adding other Actions to your Pipeline to further transform your data.

Click Run Query to execute your query


If you want to know more about Visual Mode and SQL Mode, check out the content below for more in-depth information:

Visual Mode

Chartio makes it easy for users with minimal SQL knowledge to query their data sources. With Visual Mode, you can focus on simply adding the necessary columns and filters for your chart.

Toggle Visual Mode in Visual SQL

Columns

Add a column

To add a column, click +Add Column in the Columns section. To find and add a column from the list, you can do any of the following options:

  • Search for it using the search bar

    Find column using the search bar

  • Navigate through the schema browser

    Navigate through the schema browser

  • Click on a column from the table preview of the schema browser when you select a table

    Select column from the Table Preview

Keyboard shortcuts! To add multiple columns at a time without the schema browser closing between each selection, hold Command (for Mac) or Ctrl then click the columns you wish to add. This works when selecting columns from either the schema browser or the preview table.

Note: After adding a column, you might see some schemas, tables, or columns become disabled in the schema browser when you try to add another column. This means the currently selected columns aren’t connected to those disabled items. You may need to update your data source’s foreign keys to configure your data properly.

Added columns appear in the Result Table, and each column has an Aggregation menu. The Aggregation menu is where you can choose to group by or aggregate that specific column, and the options displayed in the menu depend on the column’s data type. If you don’t want to group or aggregate a column, you can choose Unaggregated from the menu.

Each column in the Result Table has an Aggregation menu

Remove a column

To remove a column for your query, hover over the column’s block and click the X that appears on the right-side of the block.

Hover over a column and click X to delete

Filters

Only want to show data matching certain conditions? This is where filters will come in handy. For filters in a Query, you can filter by any available columns, even if they aren’t added to the Columns section.

Add a filter

You won’t be able to add a filter until you first add a column. Once you’ve added a column, you’ll see the Filters section appear to the right of the Columns section.

To add a filter, click +Add Filter in the Filters section.

  1. Selecting a column is the same process as described in Add a column.
  2. But after choosing a column, you’re prompted to choose a filter operator. The operator options vary depending on the aggregation type (e.g., group or aggregate) and data type of your selected column.
  3. After choosing the operator, you’ll need to provide values to match your data to. Type values in manually or select your Dashboard Controls, if any, from the list. Values can be literal strings, Dashboard Controls, Variables, etc.

Add a filter in a Query

All added filters are connected by an implicit AND operator. In other words, the data returned must match all the filter conditions. If you want to connect your filters with an OR operator (i.e., you only need to match some filter conditions), you’ll need to use a Filter Rows Action.

Remove a filter

To remove a filter, click the X on the right-side of the filter’s block.

Date filters and Relative Date Variables

Chartio’s Visual Mode date filters don’t necessarily follow SQL behavior. We created standard date filter operators to ensure date filtering behavior is consistent for both dates and datetimes across all our supported data sources:

  • between is exclusive, meaning the end date is not included
  • between and including is inclusive, meaning the end date is included

To apply dynamic date filters, try using Chartio’s Relative Date Variables!

Table preview

If you want to see the kinds of data stored in a particular table, you can get a convenient table preview with the first ten rows of data. When adding a column or a filter, an interactive table preview will appear in the schema browser when you select a table from the list. Hovering over any of the table’s columns in the dropdown will highlight the column in the table preview and the dropdown navigation.

Use the interactive table preview to view a snapshot of your data

Column data types

Each column in the schema browser has an icon corresponding to the column’s data type.

Data Type Icon
Boolean Icon for Boolean data type
Date or Datetime Icon for Datetime data type
Number Icon for Number data type
String Icon for String data type

Advanced Drawer

The Advanced Drawer is the collapsible section at the bottom of the Query editor. By default, the Drawer is collapsed. To expand the Drawer, click the down caret. To hide it again, click the up caret.

Use the caret toggle to open and close the Advanced Drawer in Visual Mode

There are a few notable items in the Drawer:

Query sort

Your query is automatically sorted by the order you’ve added your columns. You can change the query sort in the Advanced Drawer.

To add a sort block, click +Add Sort, select the column to sort by, then choose the sort direction (Asc for Ascending, Desc for Descending).

To delete a sort block, click the X at the right-side of the block.

To edit a sort block:

  • Click the column name to choose a different column
  • Click Asc/Desc to change the sort direction

Note: You can only sort by columns you’ve added to the Columns section.

Limit rows

Please note that Chartio queries all rows in your data source to calculate your result set, but the number of rows returned for that result set is limited to the number you enter in this field. By default, to improve performance while creating and testing your chart, the row limit for the returned results of your query is set to 1,000. When you’re finished testing, you may want to increase this limit to ensure that your chart results are not truncated by this limit. Most Chartio subscriptions allow for up to 100,000 rows to be returned from original queries.

Refer to Chart Data Limits for more details on row limits.

SQL preview

When you’re using Visual Mode, Chartio auto-generates the SQL query to be executed in the background. If you’re curious, you can see a read-only view of the auto-generated SQL query from the Advanced Drawer.

Tip! This is also a great way to learn some basic SQL—simply edit your Interactive Mode chart and watch how the generated query changes.

Check the SQL preview in Visual Mode's Advanced Drawer


SQL Mode

If you feel more comfortable creating SQL queries from scratch, you can do so by using SQL Mode in your Query Action. If you added columns or filters in Visual Mode then switched to SQL Mode, you’ll see the same auto-generated SQL query that also appears in the SQL preview in the Advanced Drawer. The key difference here is you can edit that SQL query to include more complex SQL functions that may not be feasible to do via Visual Mode.

If you need to reference the tables and columns in your data source, we’ve provided a sidebar version of Visual Mode’s schema browser on the left side of the SQL Mode editor.

Tip! Building queries in Visual Mode then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.

SQL syntax

The SQL syntax used in SQL Mode must be the syntax of the underlying data source. However, the SQL syntax in all other Actions is SQLite.

Toggle SQL Mode in Visual SQL