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;

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

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

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 1 year ago

Was this helpful?