Context: For the above table, I want to create a view "VIEW_A" with order_no, and Manufacture column on a group level.
where Manufacture = A if line level items have `manf` = A or "".
where Manufacture = B if line level items have `manf` = B
where Manufacture = Mixed if line level items have `manf` A and B.
How can I solve the issue?
You could count line items 'A' and line items 'B' and then compare the numbers with the total number of line items per order:
SELECT order_no,
CASE WHEN sum(CASE WHEN `manf` = 'A' THEN 1 END) = count(*) THEN 'A'
WHEN sum(CASE WHEN `manf` = 'B' THEN 1 END) = count(*) THEN 'B'
ELSE 'Mixed' END Manufacture
FROM mytable
GROUP BY order_no;