title: SQL Create a new column with if/else condition
body: <p>I've been working on this task for way too long and I'm still stuck. What I need to do is, working on database AdventureWorks2014, retrieve CustomerIDs and Average of orders they made (all customers of course). My problem begins when I try to insert another column (called Valuable), that is supposed to have values:</p>
  
  <ul>
  <li>'Y' if CustomerOrders > 10*AverageOfOrders and</li>
  <li>'N' if CustomerOrders    &lt; 10*AverageOfOrders.</li>
  </ul>
  
  <p>I should use CTE, that's not that important though. I did it with CASE statement, yet I keep getting an error </p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'CASE'.</p>
  </blockquote>
  
  <p>If anyone could explain to me what am I doing wrong, that would be amazing. Below is my code:</p>
  
  <pre><code>WITH CustID AS
  (
  SELECT CustomerID, COUNT(CustomerID) AS "NrOfOrdersPerCustomer"
  FROM Sales.SalesOrderHeader
  GROUP BY CustomerID
  ),
  AvgNr AS
  (
  SELECT AVG("NrOfOrdersPerCustomer") AS "AvgNrOfOrders"
  FROM CustID
  ),
  Joint AS
  (
  SELECT CustID.CustomerID, 'NULL' AS "Valuable", CustID."NrOfOrdersPerCustomer", AvgNr."AvgNrOfOrders", 10*AvgNr."AvgNrOfOrders" AS "MultipliedBy10"
      CASE 
      WHEN "NrOfOrdersPerCustomer" &gt; "MultipliedBy10" THEN 'Y'
      ELSE 'N'
      FROM CustID, AvgNr
      END
  )
  SELECT * FROM Joint; 
  </code></pre>