Column

Column charts show numerical values across categories

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;

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

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

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

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;

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;

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;

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;

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;

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;

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;

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

PreviousBubble NextCombo

Last updated 6 months ago