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: sqlfiddle.com — Thomas Junk
– Thomas Junk, Commented Aug 8, 2014 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
– RemedialBear, Commented Aug 8, 2014 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
– Narmer, Commented Aug 8, 2014 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
– Narmer, Commented Aug 8, 2014 at 15:00
Wow, you sober up quickly :P Indeed, that is what i want to do. — Tom
– Tom, Commented Aug 8, 2014 at 15:04

Arth · Accepted Answer · 2014-08-08 15:50:37Z

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 8, 2014 at 15:50

answered Aug 8, 2014 at 15:37

Arth

13.1k5 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Narmer Over a year ago

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.

Arth Over a year ago

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

Narmer Over a year ago

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.

Arth Over a year ago

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

Narmer Over a year ago

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

|

Community · Accepted Answer · 2017-05-23 12:11:39Z

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, 2017 at 12:11

CommunityBot

11 silver badge

answered Aug 8, 2014 at 15:25

Narmer

1,4547 silver badges16 bronze badges

1 Comment

Arth Over a year ago

Hmmm, see my answer, it assumes only one level of nesting however.

Collectives™ on Stack Overflow

MySQL Adding COUNT( ) values

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related