SQL to group and sum subitem and parent rows

user_19635377 0
Viewed 43

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
mysql sql
3 Answers

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
user_14918950

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
user_116950490

Here is a simpler look of the answer using self-join.

SELECT t1.name, SUM(o.commision_amount) AS commision
FROM topics t1
INNER JOIN topics t2 ON t1.id = t2.topic_id OR t1.id = t2.id
INNER JOIN orders o ON t2.id = o.topic_id
WHERE t1.topic_id IS NULL
GROUP BY t1.name;

See db<>fiddle

user_182519660
Related