Column

Column charts show numerical values across categories

Basic Column Chart

Stacked Column

Column with Series Labels

Auto Grouped Column

Column with Single Baseline

Column with Multiple Baselines for one Series

Column with Commented out Directive

Auto Column

Column with Explicit Series

Basic Column Chart

-- @chart: column
-- @title: Column - Basic Column Chart
-- @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 Month
ORDER BY Month ASC;

MONTH	SALES
January, 01 2017 00:00:00 +0000	2668855.0
February, 01 2017 00:00:00 +0000	3951547.0
March, 01 2017 00:00:00 +0000	4403443.0
April, 01 2017 00:00:00 +0000	3079040.0
May, 01 2017 00:00:00 +0000	2576305.0
June, 01 2017 00:00:00 +0000	3100093.0

Stacked Column

-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY Month, Channel
ORDER BY Month ASC;

An example of a stacked column chart

MONTH	CHANNEL	SALES
January, 01 2017 00:00:00 +0000	coldcall	174647.0
January, 01 2017 00:00:00 +0000	referral	1310064.0
January, 01 2017 00:00:00 +0000	search	681444.0
January, 01 2017 00:00:00 +0000	event	502700.0
February, 01 2017 00:00:00 +0000	pr_ad	509564.0
February, 01 2017 00:00:00 +0000	search	1109141.0
February, 01 2017 00:00:00 +0000	event	417651.0

Column with Series Labels

-- @chart: column
-- @title: Series Labels - Label at Top of Series
-- @subtitle: An example column chart with a series label above the top
-- @formats: currency
-- @series-labels: top
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
-- Select just 1 year so that the labels are not too cluttered
WHERE year(date_closed) = 2017
GROUP BY Month
ORDER BY Month ASC;

An example column chart with series labels

MONTH	SALES
January, 01 2017 00:00:00 +0000	2668855.0
February, 01 2017 00:00:00 +0000	3951547.0
March, 01 2017 00:00:00 +0000	4403443.0
April, 01 2017 00:00:00 +0000	3079040.0
May, 01 2017 00:00:00 +0000	2576305.0
June, 01 2017 00:00:00 +0000	3100093.0
July, 01 2017 00:00:00 +0000	3112476.0
August, 01 2017 00:00:00 +0000	4980193.0
September, 01 2017 00:00:00 +0000	4335105.0
October, 01 2017 00:00:00 +0000	3552154.0
November, 01 2017 00:00:00 +0000	3426988.0
December, 01 2017 00:00:00 +0000	3761158.0

Auto Grouped Column

When 1 string columns and 2 numeric columns exist

-- @title: Auto Grouped Column
-- @subtitle: Auto grouping columns when there is one category and two values
SELECT 
	Channel,
	SUM(
      CASE WHEN Sales.Status = 'Won' OR Sales.Status = 'Lost' THEN 1 ELSE 0 END
    ) as "Sales_Closed",
    SUM(
      CASE WHEN Sales.Status = 'Won' THEN 1 ELSE 0 END
    ) as "Sales_Won"
FROM sales
GROUP BY Channel;

An example of columns being automatically grouped

CHANNEL	SALES_CLOSED	SALES_WON
coldcall	206.0	37.0
referral	1069.0	243.0
search	344.0	66.0
event	427.0	112.0
pr_ad	110.0	21.0

Column with Single Baseline

-- @chart: column
-- @title: Baselines - Single Baseline
-- @subtitle: An example column chart with a default average baseline
-- @baselines: Sales
-- @series: SALES
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;

An example column chart wtih a baseline

MONTH	SALES
January, 01 2017 00:00:00 +0000	2668855.0
February, 01 2017 00:00:00 +0000	3951547.0
March, 01 2017 00:00:00 +0000	4403443.0
April, 01 2017 00:00:00 +0000	3079040.0
May, 01 2017 00:00:00 +0000	2576305.0

Column with Multiple Baselines for one Series

-- @chart: column
-- @title: Baselines - Multiple Baseline for one series
-- @subtitle: An example column chart showing sales by month
-- @formats: currency
-- @series: Sales
-- @baselines: Sales, Sales, Sales
-- @baseline-types: average, min, max
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;

An exmaple column chart with multiple baselines

MONTH	SALES
January, 01 2017 00:00:00 +0000	2668855.0
February, 01 2017 00:00:00 +0000	3951547.0
March, 01 2017 00:00:00 +0000	4403443.0
April, 01 2017 00:00:00 +0000	3079040.0
May, 01 2017 00:00:00 +0000	2576305.0
June, 01 2017 00:00:00 +0000	3100093.0
July, 01 2017 00:00:00 +0000	3112476.0

Column with Commented out Directive

-- @chart: column
-- @title: Directive Comments
-- @subtitle: An example of commenting out a directive
-- @formats: currency
-- @series: Sales
-- //@baselines: Sales
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY Month
ORDER BY Month ASC;

An example chart with @baselines commented out

MONTH	SALES
January, 01 2017 00:00:00 +0000	2668855.0
February, 01 2017 00:00:00 +0000	3951547.0
March, 01 2017 00:00:00 +0000	4403443.0
April, 01 2017 00:00:00 +0000	3079040.0
May, 01 2017 00:00:00 +0000	2576305.0
June, 01 2017 00:00:00 +0000	3100093.0

Auto Column

-- @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;

An example of an auto selected column chat

CHANNEL	WON_SALES
search	66.0
coldcall	37.0
event	112.0
referral	243.0
pr_ad	21.0

Column with Explicit Series

There are multiple columns defined, but we are only picking a specieis series using the @series directive:

-- @chart: column
-- @title: Series - Basic chart
-- @subtitle: An example of explicitly defining the series
-- @series: Sales
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales,
    sum(Profit) as Profit
FROM sales
GROUP BY Month
ORDER BY Month ASC;

An example column chart wtih more than one column but only a specific series defined

MONTH	SALES	PROFIT
January, 01 2017 00:00:00 +0000	2668855.0	801583.0
February, 01 2017 00:00:00 +0000	3951547.0	1117761.0
March, 01 2017 00:00:00 +0000	4403443.0	1224108.0
April, 01 2017 00:00:00 +0000	3079040.0	862533.0
May, 01 2017 00:00:00 +0000	2576305.0	631699.0
June, 01 2017 00:00:00 +0000	3100093.0	870893.0
July, 01 2017 00:00:00 +0000	3112476.0	871875.0

Heading2