1

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.

  • Assuming your paste above is accurate, please review your parenthesis and make sure each open paren is matched by a close paren. :) – Joishi Bodio Jul 5 '16 at 22:39
  • Sorry, no it wasn't accurate. Even with the additional close paren. after 'end' I get the same error. – Nick Jul 5 '16 at 22:41
  • Have now edited original post – Nick Jul 5 '16 at 22:42
  • The correct location for the ) is after '2015' .. WHEN doesn't require a ( after it. :) .. SUM(CASE WHEN LEFT(PERIOD,4) = '2015' THEN SALES ELSE 0 END) or SUM(CASE WHEN (LEFT(PERIOD,4) = '2015') THEN SALES ELSE 0 END) – Joishi Bodio Jul 5 '16 at 22:44
  • Thanks Joishi. Did I mention I'm new to this ;-) Really appreciate the help – Nick Jul 5 '16 at 22:46
1

You simply need to balance your ().

Either delete the ( between when and left or add a ) after '2015'.

  • Brilliant. Thanks Evan - much appreciated. All good now. – Nick Jul 5 '16 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.