按不使用 2 个字段 sum 分组 [英] Group by not working with 2 fields sum

问题描述

我有两张桌子

Id  qty  department
1   20   1
2   10   1
3   05   2

表 2:store_sub

Id   parent_id  sub_qty
1    1          5
2    1          6
3    3          9

我想生成适合这种格式的行:

I want to generate rows to fit this format:

Id  department  qty  sub_qty
1   1           30   11
2   2           05   09

我目前有以下 SQL 查询:

I currently have the following SQL query:

Select store.id,
  store.department,
  sum(store.qty),
  sum(sub_qty)
from store
  left join store_sub on store_sub.parent_id = store.id
  Group by department

但是，它不起作用.

如何获得我想要的输出?

How can I get my desired output?

推荐答案

问题是id为1的store在store_sub表中有两条对应的记录，也就是说qty 字段将在 store 1 中出现两次.您需要在子查询中分别对 2 个表求和并将求和的版本连接在一起:

The problem is that store with id 1 has two corresponding records in store_sub table, which means that qty field will appear twice for store 1. You need to sum the 2 tables separately in subqueries and join the summed versions together:

select t1.department, t1.sqty, t2.ssubqty
from
  (select department, sum(qty) as sqty from store group by department) t1
left join
  (select s2.department, s1.sum(sub_qty) as ssubqty
   from store_sub s1
   inner join store s2 on s1.parent_id=s2.id
   group by s2.department) t2 on t1.department=t2.department

这篇关于按不使用 2 个字段 sum 分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！