MySQL Adding COUNT( ) values

Question

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.

Would you mind providing a sample of what you did: http://sqlfiddle.com/ — Thomas Junk, Aug 08 '14 at 14:31
Can you post an attempt at writing the second query? If you have a starting point it will be easier for people to help you :) EDIT: @ThomasJunk beat me to it! — RemedialBear, Aug 08 '14 at 14:32
Can you create a view in your DB with first query and fetch the sum for the second view from there with `SELECT SUM(sum_offers) FROM your_view GROUP BY cat_name`? Hope I understood what you're asking, I'm not sure what main category is (is a category which has no sub-categories and you want the sum of it and all sub-categories?). — Narmer, Aug 08 '14 at 14:47
Clearly I was drunk when I wrote the above. I was asking: main category is a category which _is not a sub-category_? And you want the sum of offers for main category _and all of its sub-categories_? — Narmer, Aug 08 '14 at 15:00
Wow, you sober up quickly :P Indeed, that is what i want to do. — Tom, Aug 08 '14 at 15:04
Well, in that case it's going to be a thorn in the side since MySQL doesn't support recursive queries. The only thing left is [Hierarchical queries](http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/) which aren't the simpliest thing in the world... — Narmer, Aug 08 '14 at 15:14

Arth · Accepted Answer · 2014-08-08T15:50:37.810

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

edited Aug 08 '14 at 15:50

answered Aug 08 '14 at 15:37

Arth

12,789
5
37
69

1

In products table there is a cat_id = 15, so at least 15 categories. It is safe to say that there is no sub-sub-category? What if there is a coupé muscle-car category? Your solution works well with only 2 levels, if the OP is ok with it no problem, my answer is for a more general case. – Narmer Aug 08 '14 at 15:51
@Narmer Fair enough, the fact that OP stated they had the first query 'down' would suggest just two levels! Yes a more general case would require a function/procedure. – Arth Aug 08 '14 at 15:56
The first query doesn't care of category hierarchy. It simply selects the sum of offers for each category, regardless if its a main category or a sub-category (or sub-sub-category...). Check it in the fiddle. – Narmer Aug 08 '14 at 16:00
@Narmer you are wrong about that.. see `(categories.id = :cat_id OR categories.subcat_of = :cat_id)` in the `WHERE` clause. This clearly indicates a 2 level heirarchy. – Arth Aug 08 '14 at 16:03
1

Uh right... Missed that. Also among the specifics there is _return the names and count the amount of offers for a given main category and its subcategories_. Only OP can define if it's a 2 or more level hierarchy (which is one of the most difficult words to spell out for a non-native english speaker, I always have to google it). – Narmer Aug 08 '14 at 16:15
@Narmer, there are just 2 levels of categories indeed, the main and subcategories. No subsubsub going on here. And @Arth, thank you for your answer! I will not yet accept it, as I think it is a good thing to give other people some time to chime in. Nevertheless I really appreciate your effort. Nulling out the empty `display_names` is a good suggestion, as is omitting the ASses in my first query. Thanks a lot! – Tom Aug 08 '14 at 17:03

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

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

Emulate recursive/hierarchical queries with some complex function. (Related)
Switch to SQL Server which supports recursive queries using CTE.
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.

edited May 23 '17 at 12:11

Community

1
1

answered Aug 08 '14 at 15:25

Narmer

1,414
7
16

Hmmm, see my answer, it assumes only one level of nesting however. – Arth Aug 08 '14 at 15:47

MySQL Adding COUNT( ) values

2 Answers2