title: INSERT... SELECT... WHERE... ON DUPLICATE... mysql query
body: <p>Please help. I'm trying to update this one table with the current count of assets our products have. If the product already exist in the table, it should update the most updated count of the product. However, using the query below, mysql is returning me </p>
  
  <blockquote>
    <p>"ERROR 1111 (HY000): Invalid use of group function".</p>
  </blockquote>
  
  <p>I can't determine what's my error or if it is really valid to use count in function 'on duplicate key':</p>
  
  <pre><code>INSERT INTO report_count_assets
      SELECT products.product_id, 
      count(product_assets.asset_id),
      count(case when assets.asset_type_id=1 THEN 1 END), 
      count(case when assets.asset_type_id=2 THEN 1 END), 
      count(case when assets.asset_type_id=3 THEN 1 END), 
      count(case when assets.asset_type_id=11 THEN 1 END) 
      FROM products 
      LEFT JOIN product_assets USING (product_id) 
      LEFT JOIN assets USING (asset_id)
      WHERE products.brand_id=671
  
  ON DUPLICATE KEY UPDATE
      asset_count = count(product_assets.asset_id),
      asset_type_image = count(case when assets.asset_type_id=1 THEN 1 END), 
      asset_type_video = count(case when assets.asset_type_id=2 THEN 1 END), 
      asset_type_sound = count(case when assets.asset_type_id=3 THEN 1 END), 
      asset_type_install = count(case when assets.asset_type_id=11 THEN 1 END);
  </code></pre>