Column

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

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

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

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

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

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

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

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

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