title: MySQL Adding COUNT( ) values
body: <p><strong>EDIT:</strong> <a href="http://sqlfiddle.com/#!2/8c460/1" rel="nofollow">SQL Fiddle here</a></p>
  
  <p>I'm working on a product feed. I get a list of offers;</p>
  
  <ul>
  <li>Every offer has 1 product</li>
  <li>Every product belongs to 1 category</li>
  <li>A category can be a subcategory to another category</li>
  </ul>
  
  <p>I have three tables (of which i will only show you the relevant rows)</p>
  
  <p>Offers:</p>
  
  <pre><code>OFFERS
  ___________________
  | id | product_id |
  -------------------
  |  1 |         16 |
  -------------------
  |  2 |         54 |
  -------------------
  |  3 |         52 |
  -------------------
  |  4 |         20 |
  -------------------
  |  5 |          7 |
  -------------------
  |  6 |          5 |
  -------------------
  </code></pre>
  
  <p>Products:</p>
  
  <pre><code>PRODUCTS
  _______________
  | id | cat_id |
  ---------------
  | 16 |      1 |
  ---------------
  | 54 |      3 |
  ---------------
  | 52 |      4 |
  ---------------
  | 20 |      1 |
  ---------------
  |  7 |     15 |
  ---------------
  |  5 |      3 |
  ---------------
  </code></pre>
  
  <p>Categories:</p>
  
  <pre><code>CATEGORIES
  _____________________________________________________________
  | id | display_name | original_name | subcat_of | is_active |
  -------------------------------------------------------------
  |  1 | Cars         | automobiles   |         0 |         1 |
  -------------------------------------------------------------
  |  2 |              | motorcycles   |         0 |         0 |
  -------------------------------------------------------------
  |  3 | Muscle cars  | muscle-cars   |         1 |         1 |
  -------------------------------------------------------------
  |  4 | Hybrid cars  | treehugwagons |         1 |         1 |
  -------------------------------------------------------------
  </code></pre>
  
  <p>I have to write two queries. The first one needs to</p>
  
  <ul>
  <li>return the names and count the amount of offers for a given main category and its subcategories</li>
  <li>but only if that main category is active</li>
  <li>and if a category has no display_name, use the original_name</li>
  </ul>
  
  <p>I think i have this one down:</p>
  
  <pre><code>SELECT
      offers.id AS offer_id,
      product_id,
      products.cat_id,
      CASE
          WHEN categories.display_name &lt;&gt; ''
          THEN categories.display_name
          ELSE categories.original_name
      END AS cat_name,
      COUNT(offers.id) as num_offers
  FROM
      offers
  INNER JOIN
      products
      ON
      product_id = products.id
  INNER JOIN
      categories
      ON
      cat_id = categories.id
  WHERE
      categories.is_active = 1
      AND
      (categories.id = :cat_id OR categories.subcat_of = :cat_id)
  GROUP BY
      cat_name
  ORDER BY
      cat_name ASC
  </code></pre>
  
  <p>I'm pretty sure this query is far from ideal, but for now, it works.</p>
  
  <p>It is the second query I need that gives me problems. That one needs to:</p>
  
  <ul>
  <li>return the names and count the amount of offers for a given main category and its subcategories <strong>and return the sum of those counts per main category</strong></li>
  <li>but only if that main category is active</li>
  <li>and if a category has no display_name, use the original_name</li>
  </ul>
  
  <p>I could use some PHP to do the summing myself, but I'd be surprised if something that easy could not be done in SQL.</p>