SQL to group and sum subitem and parent rows-CodePudding

I'm having issues trying to figure out how to group and sum subitem with parent item rows.

I have a SQL query that looks like this:

select 
  topics.name,
  sum(commission_amount) as commission
from orders
left join topics
on topics.id = orders.topic_id
group by 1

This works, however I'm trying to group and use only parent topic names.

Topics table:

id	name	topic_id
1	Meal Delivery	NULL
2	Vegan Meal Delivery	1
3	Vegetarian Meal Delivery	1
4	Mattresses	NULL
5	Hybrid Mattress	4
6	Memory Foam Mattress	4

So a parent topic is when topic_id = NULL

Orders table:

id	topic_id	commission_amount
1	1	10
2	2	20
3	3	30
4	4	40
5	5	50
6	6	60

Desired output is this:

name	commission
Meal Delivery	60
Mattresses	150

CodePudding user response：

Join with topics again.

SELECT name, SUM(commission) AS commission
FROM (
    -- subtopic commissions
    SELECT t1.name, IFNULL(SUM(o.commission_amount), 0) AS commission
    FROM topics AS t1
    LEFT JOIN topics AS t2 ON t1.id = t2.topic_id
    LEFT JOIN orders AS o ON o.topic_id = t2.id
    WHERE t1.topic_id IS NULL -- only show parent topics
    GROUP BY t1.name
    
    UNION ALL
    -- parent topic commissions
    SELECT t.name, IFNULL(SUM(o.commission_amount), 0) AS commission
    FROM topics AS t
    LEFT JOIN orders AS o ON o.topic_id = t.id
    WHERE t.topic_id IS NULL
    GROUP BY t.name
) AS x
GROUP BY name

CodePudding user response：

You can do a self-join to bring all parent topics, and children to the same level:

select TPnt.Name, sum(O.Commission_amount) as Commission_amount
from 
     Topics TPnt

     inner join
     Topics TChld
     on TPnt.Id=coalesce(TChld.topic_id, TChld.id)

     inner join
     Orders O
     on O.topic_id=TChld.id

group by TPnt.Name