Quick Start

ChartSQL allows you to transform any SQL query into interactive charts. This guide will walk you through the key features of ChartSQL.

ChartSQL Key Concepts

Any SQL Query Can Be a Chart

A valid SQL query is a valid ChartSQL chart. ChartSQL will inspect the SQL result and visualize your query. You can also annotate your SQL to direct ChartSQL how to visualize your query. We call these annotations 'at directives'

-- @chart: column
-- @title: Column - Sales by Month
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC

There are many directives to control plotting, style and data management like @chart, @series, @formats

See Directives for all of the available directives.

What You Query is What You Chart

We call it ‘Whikee-Whic’. ChartSQL charts the data, as it exists, in the order it is returned by the query. It does not shape your data in any way unless told to. It does not make assumptions about how you intended to group your series unless you tell it to. It does not require you to provide aggregation directions. ChartSQL assumes your data is already aggregated.

Advanced data shaping can be done in the full power of SQL. Using SQL to shape data simplifies the charting layer.

ChartSQL Studio

The easiest way to get started with ChartSQL is with ChartSQL Studio, our source code editor for visualizing SQL.

Studio allows you to create ChartSQL scripts, execute SQL statements and generate visualizations.

Understanding ChartSQL Detection Modes

ChartSQL will assist in automatically creating your charts, in three ways:

1. Auto:

   1. Simply run your SQL queries and ChartSQL will automatically select a chart type for you based on the column data types. This is the best approach when you're exploring data or need quick visual insights. There are specific rules for the auto charts as described in Auto Mode

2. Assist:

   1. If you want to create a specific type of chart, use the @chart directive to guide ChartSQL. For instance, @chart: line will tell ChartSQL to create a line chart. ChartSQL will then auto-detect and organize the remaining columns.

3. Manual:

   1. For complete control over the chart, use the @chart directive alongside other plotting directives like @groups and @series (see Directives). Manually specifying directives is useful for creating more complex visualizations.

Auto Mode

ChartSQL's auto mode eases the process of creating visualizations by inferring the best chart from your SQL query column data types.

ChartSQL will use the following data ruleset to determine the type of chart to create.

Example 1: Auto-Detected Column Chart

When your data includes one non-numeric (category) and one numeric column (series), ChartSQL will automatically display this as a column chart.

SQL Query:

-- @title: Auto Column
-- @subtitle: An example of an auto selected column chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel;

Resulting Chart: A column chart where each column represents the total deals won for the channel. The x-axis lists the channels, and the y-axis shows the count of won.

Example 2: Auto-Detected Line Chart

For data with one date or datetime column and multiple numeric columns, ChartSQL generates a line chart.

SQL Query:

-- @title: Auto Line - Auto Generated Date Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

Resulting Chart: ChartSQL produces a date line chart. The Date column is used for the x-axis, while the numeric columns form the y-axis values.

Example 3: Auto-Detected Heatmap

If your dataset includes one numeric column and two non-numeric columns, ChartSQL will create a heatmap.

SQL Query:

-- @title: Auto Heatmap
-- @subtitle: An example auto generated heatmap comparing two categories
SELECT
Owner,
Channel,
count(*) as Sales
FROM Sales
WHERE Sales.Status = 'Won'
GROUP BY Owner, Channel;

Resulting Chart: A heatmap displaying the total count of sales across the channel and owner dimensions. One non-numeric column defines the x-axis, the other non-numeric column defines the y-axis, and the numeric column determines the color intensity.

Example 4: Auto-Detected Scatter Chart

When the result set contains exactly two numeric columns, ChartSQL will auto-detect a scatter chart.

SQL Query:

-- @title: Auto Scatter
-- @subtitle: Auto generted scatter chart
SELECT 
  Profit,
  Amount
FROM Sales
WHERE Status = 'Won';

Resulting Chart: A scatter chart where the Profit is related to the sale Amount

Limits of Auto Detection.

These examples demonstrate how ChartSQL's auto detection feature takes the guesswork out of creating charts. Auto detection relies on specific number and types of columns being present. If that doesn’t work for your needs, you can start using directives to fully control the chart.

Assist Mode

When you need more control than Auto detection allows, you can add annotations to direct ChartSQL how to render your chart. For a bar chart, add -- @chart: bar at the beginning of your SQL. These annoations are called 'at directives'.

-- @chart: bar
-- @title: Bar Chart
-- @subtitle: An basic example of a bar chart
SELECT 
Channel,
count(*) as Won_Sales
FROM Sales
WHERE Status = 'Won'
GROUP BY Channel
ORDER BY count(*) ASC;

The directive '@chart: bar" instructs ChartSQL to render a bar chart. ChartSQL will attempt to detect which columns to use for the category and series from left to right. If you are not getting the results you need, then you can use the manual plotting directives described next.

Manual Mode

Beyond the chart type, you sepecify the exact @category and @series to use for your chart. This is necessary when your result set has more columns than you want to visualize, you need to override the which columns are chosen by ChartSQL, or change their order.

• @category to specify the x-axis or pie segments

• @series to set the series for bar, column, scatter, or line charts. Provide a single column for simple charts or multiple columns for multi-series charts

Here's an example of a query that returns multiple potential categories and series, but we only want to visualize certain columns:

-- @chart: line
-- @title: Manual Category and Series
-- @subtitle: An example chart manually setting the category and series
-- @category: Date_Closed
-- @series: Amount
SELECT 
    Sub.*,
    (
      SELECT channel
      FROM Sales
      WHERE Sales.Date_Closed = Sub.Date_Closed
      GROUP BY Channel
      ORDER BY count(*) DESC
      LIMIT 1
    ) as TopChannel
FROM (
    SELECT 
      Date_Closed,
      sum(Amount) as Amount,
      sum(Profit) as Profit
    FROM Sales
    WHERE Status = 'Won'
    GROUP BY Date_Closed
    ORDER BY Date_Closed ASC
) as Sub;

Using Secondary Series

For dual-axis charts, use @secondary-series to put series on the right side axis. You will often want this for combo charts where you need to visualize different series at different scales.

-- @chart: combo
-- @title: Combo - Column Chat with Secondary Line
-- @subtitle: A combo chart with a column series and a secondary line series
-- @category: Channel
-- @series: TotalDeals
-- @secondary-series: ConversionRate
-- @series-types: column, line
-- @formats: currency, percent
SELECT 
	Sub.*,
    ROUND(WonDeals / TotalDeals * 100, 1) as ConversionRate
FROM (
  SELECT 
  Channel,
  CAST(count(*) AS FLOAT) as TotalDeals,
  SUM(CASE WHEN Status = 'Won' THEN 1 ELSE 0 END) as WonDeals
  FROM Sales
  WHERE Status = 'Won' or Status = 'Lost'
  GROUP BY Channel
) as Sub

Next Steps

For further details on each chart type and directive, visit the Overview page and the Directivesfor in-depth guidance.