Add column containing status to table based on number of occurrences

Question

My SQL table contains two columns: Order and Order Line. Now I would like to add a third column (Status) to my table to indicate whether it's a single or multi line order. If the order number only occurs only once then 'Single line' else 'Multi line'. How can I do this? Thanks!

Order	Order Line	Status
10000	10	Single line
10001	10	Multi line
10001	20	Multi line
10002	10	Single line

Answer 1

With window functions (Also called Analytics Functions or Ordered Analytical Functions):

SELECT Order, 
  OrderLine, 
  CASE WHEN COUNT(OrderLine) OVER (PARTITION BY Order) > 1 THEN 'Mult line' ELSE 'Single line' END as Status
FROM yourtable;