title: How do I get Counts/Flag correctly using case expressions in Oracle
body: <p>I have a table with fields and values that look like this.</p>
  
  <pre><code>CustomerID    OrderMedium     NumberofOrder
  101             Computer           5
  101             Phone              2
  102             Computer           9
  103             Computer           1
  103             Phone              6
  104             Phone              2
  105             Computer           3
  105             Phone              3
  </code></pre>
  
  <p>From the above table, I am trying to create another table with fields CustomerID, OrderMedium, OnetoFiveOrders, SixtoTenOrders, Count. </p>
  
  <p>The customerID field should contain the CustomerID, OrderMedium field should contain the OrderMedium, OnetoFiveOrders should contain a flag of 'Y' if a customer has between one to five orders, SixtoTenOrders should contain a flag of 'Y' if a customer has between Six to Ten orders, and count should contain the Number of Orders.  </p>
  
  <p>So for example, CustomerID 101 with an OrderMedium of Computer will have a flag of 'Y' under OnetoFiveOrders and count of 5 since they have 5 orders. </p>
  
  <p>The problem I'm having is that if the same customer with a different Order Medium has a total order count that is greater than 5 then it should have a flag of 'Y' under SixtoTenOrders, but still give the number of order for that medium </p>
  
  <p>So For Customer 101, the table should look like this.  </p>
  
  <pre><code>CustomerID    OrderMedium     OnetoFiveOrders   SixtoTenOrders     Count
  101             Computer           Y                                 5
  101             Phone                                Y               2
  </code></pre>
  
  <p>Since the customer has a total Order count of 7, it should give a flag of 'Y' under SixtoTenOrders and a count of 2 because they have 2 orders under the Phone Order Medium. </p>
  
  <p>I'm having problems getting my code to work/output this way and would appreciate any kind of help or suggestion.</p>
  
  <p>This is my my code so far:</p>
  
  <pre><code>Select CustomerID, OrderMedium, NumberofOrder as Count, case when               NumberofOrder between 1 and 5 then 'Y' else
  case when NumberofOrder between 6 and 10 then 'Y'
  else null
  end as OnetoFiveOrders
  end as SixtoTenOrders
  from CustOrder;
  </code></pre>