Re: GROUP BY and NULL values

[Mason Sklut]

When I do that it still produces the same output. You're suggesting this, right?

SELECT 
 `Order ID`, 
  MAX(`Qty`) AS `Qty`, 
  MAX(`Option`) AS `Option`,
  `Style`,
  MAX(`Product`) AS `Product`
FROM (
  SELECT
   `o`.`order_id` AS `Order ID`,
   `op`.`quantity` AS `Qty`,
    CASE
      WHEN null then ''
      WHEN `oo`.`name` LIKE 'Color%' THEN `oo`.`value` 
      ELSE ''
    END AS `Option`,
    CASE 
      WHEN null then ''
      WHEN `oo`.`name` = 'Color - Current' THEN 'C' 
      WHEN `oo`.`name` = 'Color - Vintage' THEN 'V'
      WHEN `oo`.`name` = 'Collar' THEN `oo`.`value`
      ELSE ''
    END AS `Style`,
    `op`.`name` AS `Product`
  FROM  `order` AS `o`
  INNER JOIN `order_product` AS `op` ON `op`.`order_id` = `o`.`order_id`	 
  LEFT JOIN  `order_option`  AS `oo` ON `o`.`order_id` = `oo`.`order_id` 
                                     AND `op`.`order_product_id` = `oo`.`order_product_id`  
) AS `Orders`
GROUP BY `Order ID`, `Style`
ORDER BY `Order ID` ASC;

OUTPUT:

| Order ID | Style | Qty | Option      | Product  |
| -------- | ----- | --- | ----------- | -------- |
| 12345    | C     | 1   | Classic Red | ProductA |
| 12345    | V     | 1   | Classic Red | ProductA |
| 12346    |       | 1   | Grey        | ProductB |
| 12346    | Y     | 1   |             | ProductB |