MySQL Adding COUNT( ) values

Question

EDIT: SQL Fiddle here

I'm working on a product feed. I get a list of offers;

• Every offer has 1 product
• Every product belongs to 1 category
• A category can be a subcategory to another category

I have three tables (of which i will only show you the relevant rows)

Offers:

OFFERS
___________________
| id | product_id |
-------------------
|  1 |         16 |
-------------------
|  2 |         54 |
-------------------
|  3 |         52 |
-------------------
|  4 |         20 |
-------------------
|  5 |          7 |
-------------------
|  6 |          5 |
-------------------

Products:

PRODUCTS
_______________
| id | cat_id |
---------------
| 16 |      1 |
---------------
| 54 |      3 |
---------------
| 52 |      4 |
---------------
| 20 |      1 |
---------------
|  7 |     15 |
---------------
|  5 |      3 |
---------------

Categories:

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 |
-------------------------------------------------------------

I have to write two queries. The first one needs to

• return the names and count the amount of offers for a given main category and its subcategories
• but only if that main category is active
• and if a category has no display_name, use the original_name

I think i have this one down:

SELECT
    offers.id AS offer_id,
    product_id,
    products.cat_id,
    CASE
        WHEN categories.display_name <> ''
        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

I'm pretty sure this query is far from ideal, but for now, it works.

It is the second query I need that gives me problems. That one needs to:

• return the names and count the amount of offers for a given main category and its subcategories and return the sum of those counts per main category
• but only if that main category is active
• and if a category has no display_name, use the original_name

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.

Answer 1

I believe it is possible and fairly simple, assuming you don't have sub-sub-categories:

  SELECT CASE 
           WHEN c_main.display_name <> ''
           THEN c_main.display_name
           ELSE c_main.original_name
         END cat_name,
         COUNT(o.id) as num_offers
    FROM offers o
    JOIN products p
      ON o.product_id = p.id
    JOIN categories c
      ON p.cat_id = c.id
     AND (c.id = :cat_id OR c.subcat_of = :cat_id)
  /* AND c.is_active = 1 /* Include if necessary */
    JOIN categories c_main
      ON c_main.id = :cat_id
     AND c_main.is_active = 1 
GROUP BY cat_name
ORDER BY cat_name ASC

Your first query I would write as:

  SELECT CASE 
           WHEN c.display_name <> ''
           THEN c.display_name
           ELSE c.original_name
         END cat_name,
         COUNT(o.id) as num_offers
    FROM offers o
    JOIN products p
      ON o.product_id = p.id
    JOIN categories c
      ON p.cat_id = c.id
     AND (c.id = :cat_id OR c.subcat_of = :cat_id)
     AND c.is_active = 1
GROUP BY cat_name
ORDER BY cat_name ASC

As an aside:

I would also consider NULLing out display_names that are empty, then you can replace

CASE 
  WHEN c_main.display_name <> ''
  THEN c_main.display_name
  ELSE c_main.original_name
END cat_name

With:

COALESCE(c_main.display_name, c_main.original_name) cat_name

Answer 2

It can't be done in MySQL since MySQL doesn't support recursive queries. You have three choices:

1. Emulate recursive/hierarchical queries with some complex function. (Related)
2. Switch to SQL Server which supports recursive queries using CTE.
3. Write a bit of PHP.

It's up to you :)

As a side note i leave here an interesting slide on Models for Hierarchical data with SQL and PHP written by SO user @Bill Karwin which may suit your situation.