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.201501
is January 2015;Customer_no
;Sales
;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.
Best Answer
You simply need to balance your
()
.Either delete the
(
betweenwhen
andleft
or add a)
after'2015'
.