Re: GROUP BY and NULL values

[Peter Brawley]

It's a public forum. For the benefit of interested readers, here's the query under discussion ...

SELECT 
 `Order ID`, 
  MAX(`Qty`) AS `Qty`, 
  MAX(`Option`) AS `Option`,
  MAX(`Style`) AS `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`
ORDER BY `Order ID` ASC;

You could lose the Max() call for style and add style to the Group By clause.