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!

CodePudding user response：

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;