I'm very new to SQL and I'm trying and failing to get the right syntax on what I presumed would be a pretty easy query.
I have a table (CustomerSales) with four columns:
Period: a combination of year and month, e.g.
201501is January 2015;
Class, i.e. customer segment.
I currently have the below query which extracts all sales in 2015 for class 'R', by customer number:
select Customer_no, class, SUM (Sales) as '2015' from CustomerSales where left(Period,4) = '2015' and class = 'R' Group by Customer_no, class
This returns a simple 3-column output of Customer_no, class and 2015 Sales.
I would like to add columns for each year in the table, i.e. add five more columns for each year 2010-2014.
To do this, I tried to move the
left(Period,4) = '2015' expression into a
case when condition and then my intention was to repeat this for 2014, 2013, etc. But the query fails before I even try to include the other years. The current query (with just 2015) is:
select Customer_no, class, SUM (case when(left(Period,4) = '2015' then Sales else 0 end)) as '2015' from CustomerSales where class = 'R' Group by Customer_no, class
The error I get is
Incorrect syntax near the keyword 'then'.
I'd be grateful for any help as to how to fix the above query and add the 2010-2014 sales as extra columns.