SQL nested sums with multiple joins and a group

Question

I am not very familiar with SQL. I use the oracle. I met a question with summation of fields.

Here are sample tables:

A: A_ID A_NAME B: B_ID A_ID B_NAME B_QTY C: C_ID B_ID C_QTY 

So the data structure is similar to A → * B → * C

I need to get the total number of Bs and Cs grouped by B_NAME and A_ID. For instance:

 A: A_ID A_NAME 1 A1 B: B_ID A_ID B_NAME B_QTY 1 1 B1 20 2 1 B1 5 3 1 B1 5 4 1 B2 5 C: C_ID B_ID C_QTY 1 1 3 2 1 4 4 2 2 5 2 1 6 3 1 7 4 1 

Expected Result:

 A_ID A_NAME B_NAME B_QTY C_QTY 1 A1 B1 30 11 1 A1 B2 5 1 

30 of B_QTY in the 1st row are the result of 20 + 5 + 5.

11 of C_QTY in the 1st row is the result of 3 + 4 + 2 + 1 + 1.

Here is my sql:

 select a.A_ID, a.A_NAME, b.B_NAME sum(b.B_QTY), sum(c.C_QTY) from A a left outer join B b on b.A_ID = a.A_ID left outer join C c on c.B_ID = b.B_ID group by a.A_ID order by a.A_ID, b.B_NAME where a.XXXX = XXXXX; 

So the problem is this:

Since B goes over to several Cs, B_QTY will be summed up several times. I am not very familiar with SQL, so I don’t know if there is any simple way to determine the sum based on some fields (this is B_ID in my example). Thanks!

Answer 1

This can also be done as follows:

 WITH b2 AS (SELECT b.*, sum(b.b_qty) over (partition BY b.a_id, b.b_name) b_qty_s FROM b) SELECT a.a_id, a.a_name, b2.b_name, b2.b_qty_s, sum(c.c_qty) c_qty_s FROM a JOIN b2 ON a.a_id = b2.a_id JOIN c ON b2.b_id = c.b_id GROUP BY a.a_id,a.a_name, b2.b_name, b2.b_qty_s 

Here is the sqlfiddle daemon

Answer 2

I created an SQL script for this problem . The trick is that B_QTY has appeared in your results more than once. To summarize, he gave artificially high value. So instead, run an extra select to use B_NAME only once! Great question !: ^ D

ABCade's answer is cool, but this solution will work for many databases. I have used this method before with SQL Server, Oracle, and Informix.

Data / Schemes:

 create table a (A_ID int, A_NAME char(10)); create table b (B_ID int, A_ID int, B_NAME char(10), B_QTY int); create table c (C_ID int, B_ID int, C_QTY int); -- One dude insert into a values (1,'Xiezi'); -- 2 orders? of 4 and 3 insert into b values (1,1,'B1',20); insert into b values (2,1,'B1',5); insert into b values (3,1,'B1',5); insert into b values (4,1,'B2',5); -- 2 order with 2 lines each. insert into c values (1,1,3); insert into c values (2,1,4); insert into c values (4,2,2); insert into c values (5,2,1); insert into c values (6,3,1); insert into c values (7,4,1); 

SQL (answer):

 select a.A_ID, a.A_NAME, b.B_NAME, (select sum(b2.B_QTY) from b b2 where b2.B_NAME = b.B_NAME) as sum_b_qty, sum(c.C_QTY) from a left outer join b on b.A_ID = a.A_ID left outer join c on c.B_ID = b.B_ID group by a.A_ID, a.A_NAME, b.B_NAME order by a.A_ID ; 

Output:

 A_ID A_NAME B_NAME SUM_B_QTY SUM(C.C_QTY) 1 Xiezi B1 30 11 1 Xiezi B2 5 1 

Answer 3

You can also do the following:

 SELECT DISTINCT A_ID,A_NAME,B_NAME,B_SUM,SUM(C_QTY) OVER(PARTITION BY A_NAME,B_NAME) C_SUM FROM ( SELECT A.A_ID,A_NAME,B_NAME,B_ID,SUM(B_QTY) OVER(PARTITION BY A_NAME,B_NAME) B_SUM FROM A JOIN B ON A.A_ID=B.A_ID) T1 JOIN C ON T1.B_ID=C.B_ID