sql groupBy

SELECT 
  product.id AS id, 
  product.image AS image, 
  product_additional_val.count_color AS count_color, 
  product_additional_val.url AS url 
FROM 
  product 
  INNER JOIN (
    SELECT 
      COUNT(product_additional.cid) AS count_color, 
      product_additional.* 
    FROM 
      product_color 
      INNER JOIN (
        SELECT 
          product_additional_sub.pid AS pid, 
          product_additional_sub.url AS url, 
          product_additional_sub.article AS article, 
          product_additional_sub.cid AS cid 
        FROM 
          product_additional AS product_additional_sub 
        WHERE 
          is_active = '1' 
        ORDER BY 
          sorting ASC
      ) AS product_additional ON product_additional.cid = product_color.id 
    GROUP BY 
      product_additional.pid
  ) AS product_additional_val ON product.id = product_additional_val.pid 
WHERE 
  product.is_active = '1' 
GROUP BY 
  product.id 
ORDER BY 
  product.sorting ASC, 
  product.id DESC