How to get a sum of a relational table joined with a grouped result set?

Question

The following query can be used to get the amount of stock alerts set for all products:

SELECT `product_id`, COUNT(`alert_stock_id`) AS `qty_alert` 
FROM `product_alert_stock` 
GROUP BY `product_id`

For configurable products (a product which has child products), how can I get the sum of its child products as well as its own in one query?

So if a product has children in catalog_product_super_link, the count of all the product_id should be retrieved from product_alert_stock and added to that parent product its count.

Example data in catalog_product_super_link

+---------+------------+-----------+
| link_id | product_id | parent_id |
+---------+------------+-----------+
|    1    |          2 |         1 |
|    2    |          3 |         1 |
|    3    |          4 |         1 |
+---------+------------+-----------+

Example result set of the query supplied earlier:

ID 1: 0 stock alerts
ID 2: 5 stock alerts
ID 3: 2 stock alerts
ID 4: 2 stock alerts

The desired result set should be:

+------------+-----------+
| product_id | qty_alert |
+------------+-----------+
|          1 |         9 | # 0 + 5 + 2 + 2
|          2 |         5 |
|          3 |         2 |
|          4 |         2 |
+------------+-----------+

This is probably a simple join or subquery problem, but I can't get it figured out in one query with an unidentified data set (i.e. no product ID filter).

EDIT @ 14-09-2015 // Further clarification

There might not be any results in product_alert_stock, then qty_alert is 0.

This query is supposed to be used inside a sub query (JOIN):

SELECT `e`.`entity_id` as `e_product_id`, `qty_alert` -- [...]

FROM `catalog_product_entity` as `e`

LEFT OUTER JOIN (
    -- [...] 
    -- query to retrieve the alert count als described in question, with `qty_alert` as identifier
) ON `e`.`entity_id` = `xyz`.`product_id`
-- `xyz` being the table holding `product_id` and `qty_alert` values to join

-- [...]

Tried fitting the given answers below into the sub query but it does not yield the correct result set, see the comment below the answers.

PS. If anyone wonders, these tables come from Magento.

you have 0 5 2 2. Is that 0 for parent, and the rest for its children? Also, how is that combined # even meaningful (9) ? — Drew, Sep 08 '15 at 11:11
MySQL has very limited support for recursive procedures, and no support whatsoever for recursive functions; it is therefore not very well suited to processing hierarchical data stored in the adjacency-list format (such as this). You would do well to model your data differently, such as with nested sets or a transitive closure table. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. — eggyal, Sep 08 '15 at 11:19
Yes, 0 for parent and the rest for the children. The data is meaningful because the parent is the base product which may or may not be sold, the stock alerts for the sum of the variations (children of the base product, for example M, S, XL) can help indicate how popular a product is. — Elias, Sep 08 '15 at 12:29

Hirvita Shah · Answer 1 · 2015-09-08T12:00:31.820

2

you can use left join function to combine both answer

SELECT  `product_id`,
         COUNT(`alert_stock_id`) + COALESCE(alert,0) AS `qty_alert` 
FROM    `product_alert_stock` T1 
         LEFT OUTER JOIN 
         (
            SELECT   parent_id,count(1) `alert` 
            FROM     catalog_product_super_link 
            GROUP BY parent_id 
          )T2 
            ON T1.product_id = T2.parent_id
GROUP BY `product_id`;

edited Sep 08 '15 at 12:00

answered Sep 08 '15 at 11:40

Hirvita Shah

41
4

Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually won't help the OP to understand their problem. – Reeno Sep 08 '15 at 11:46
Yes, looks better now – Reeno Sep 08 '15 at 12:09
Doesn't seem to return results for rows which aren't in `product_alert_stock`. From the example in the question, product_id = 1 doesn't have any rows in `product_alert_stock`. – Elias Sep 14 '15 at 09:40
After closer inspection, the query cannot be correct as it counts the rows in `catalog_product_super_link`. – Elias Sep 14 '15 at 10:58

Dewfy · Answer 2 · 2015-09-14T10:07:52.807

Question is rather simple if folding level of products is 1. You need nested query (sorry have no access to mysql now to check)

SELECT s.product_id, (s.qty_alert + c.qty_alert) total FROM (
  --subquery for top-products
  SELECT `product_id`, COUNT(`alert_stock_id`) AS `qty_alert` 
     FROM `product_alert_stock`
     GROUP BY `product_id` ) s LEFT OUTER JOIN
  (--subquery for children
  SELECT l.parent_id as `product_id`, COUNT(`alert_stock_id`) AS `qty_alert` 
      FROM `product_alert_stock` a inner join catalog_product_super_link l
           ON a.product_id = l.product_id -- selects only children
      GROUP BY l.parent_id ) c
on s.product_id = c.product_id -- set match between child and super

score 1 · Answer 3 · answered Sep 08 '15 at 11:22

1

Assuming it is a single level parent / child relationship you can use LEFT OUTER JOIN:-

SELECT t1.product_id, COUNT(DISTINCT t1.alert_stock_id) + COUNT(t3.alert_stock_id) AS qty_alert 
FROM product_alert_stock t1
LEFT OUTER JOIN catalog_product_super_link t2
ON t1.product_id = t2.parent_id
LEFT OUTER JOIN product_alert_stock t3
ON t2.product_id = t3.product_id
GROUP BY t1.product_id

This counts the distinct stock ids for the parent, and all of them for the child.

answered Sep 08 '15 at 11:22

Kickstart

21,403
2
21
33

Due to the use of DISTINCT, this query fails if there is more than one set (not nodes, it's only single level) of unique parent-childs. – Elias Sep 14 '15 at 11:07
1

Can you put an example on sql fiddle please? – Kickstart Sep 14 '15 at 13:25

How to get a sum of a relational table joined with a grouped result set?

EDIT @ 14-09-2015 // Further clarification

3 Answers3