Sql-server – SUM (CASE WHEN) AS.. query

casesql serversum

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 ( between when and left or add a ) after '2015'.