Custom SQL
Chartio allows you to manually edit the SQL queries used to fetch the data. Simply click on "SQL Mode" in the right-hand side of the dataset window.
Let's see what SQL mode looks like with an example query.
The following query, for example, is the basic query for plotting the number of users who joined each day.
SELECT DATE(`users`.`date_joined`) as x, COUNT(`users`.`id`) as y FROM `users` GROUP BY x ORDER BY x;
From the syntax you might guess that the first column will be plotted on the X axis.
DATE(`users`.`date_joined`) as x
and the second column will be plotted as the Y axis.
COUNT(`users`.`id`) as y
X Axis
The X axis can support a column of any value. Typically, you want to GROUP your results by this column, as seen in the above query’s “GROUP BY x” clause.
Additionally, ordering by this value will ensure that your results are plotted in order and in the direction you desire. If the graph is the opposite of what you intended to see (in other words, backwards), then order by the column in DESC order: “ORDER BY x DESC”;
Date Fields on the X Axis
Time series charts are a vital and common component of any dashboard and Chartio works some extra magic to create those charts. You can tap into these extra features by using the following date formats when aggregating by Day, Week, Month or Year. Below are some different date functions along with examples.
| Granularity | Function | Example |
|---|---|---|
| Day | DATE | DATE(`users`.`date_joined`) as x |
| Week | YEARWEEK | YEARWEEK(`users`.`date_joined`) as x |
| Month | DATE_FORMAT(col, ‘%Y-%m’) | DATE_FORMAT(`users`.`date_joined`, ‘%Y-%m’) as x |
| Year | YEAR | YEAR(`users`.`date_joined`) as x |
Note for UNIXTIMESTAMP users: If you are using UNIXTIMESTAMPs instead of the MySQL DATETIME data types, you first need to wrap your columns in the FROM_UNIXTIMESTAMP conversion function.
DATE(FROM_UNIXTIMESTAMP(date_joined)) as x
Alternatively, you can do the conversion visually by clicking on the column's icon and switching to the clock icon.
The Y Axis
The Y axis can be of any type. Its usually desirable to use some operator such as COUNT, SUM or AVG on the results when the X axis is GROUPed. See the full list of functions here.
Adding Filters (where clauses)
Chartio only cares about the columns that are returned in your query. The rest of the query can be constructed however you like as long as it returns those two columns (the X and the Y). You can include multiple tables, perform joins and WHERE filters. You can even use subqueries.
Adding a Third Column
The first two columns returned in the query results refer to X and Y and if a 3rd column is returned it is used as a split column for creating multiple data sets. Be sure to GROUP by this column in your SQL definition.
The following example shows the SQL and the resulting graph for finding the number posts happening each day in the StackOverflow example dataset. The # of posts are split by whether they are a question or an answer.
SELECT YEARWEEK(`post`.`created`) as x, COUNT(`post`.`id`) as y, `post`.`post_type_id` as z
FROM `post` WHERE `post`.`created` > FROM_UNIXTIME(998517850) GROUP BY x, z ORDER BY x, z
Cumulative Result
In some situations, you might want to see a running total of results rather than the day to day change. For example, it's nice to see a graph of the running count of your users in addition to a graph of how many new users you’re bringing in each individual day. You can achieve this through a subquery, but its much easier on your server if the results are just sequentially summed up after they are returned. Let's illustrate this with an example. On the left hand side we see how many users we're adding each individual day. On the right hand side we see a running total of our userbase.
Sessions Settings
If you like you can paste multiple query commands into the custom SQL input if they are separated by semicolons. If you want to change the isolation level of your session for example you can prepend your query with the command to adjust. To set the isolation level for example you can prepend your query as shown here.
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DATE(`users`.`date_joined`) as x, COUNT(`users`.`id`) as y
FROM `users` GROUP BY x ORDER BY x;
Limits
The chartio charts will automatically limit your queries to 400 pieces of data. Charts that go over that are usually quite useless to read and dramatically slow down your browser.
If you run into a problem with the limits consider accumulating your X axis results on a larger scale. Aggregate results by Week instead of Day for example. At that resolution you’ll hardly notice a difference.
If you have any trouble with connecting please do not hesitate to contact us at SQL@chart.io.
