CODEWITHSUNDEEP
phpmysqlsqlsum
Fedri Andhika
Fedri Andhika

Reputation: 23

Sum of field from 5 table on MySQL

I have 5 tables on MySQL, following:

Table A
ID-A  | NAME
A1    | Name A
A2    | Name b

Table B
ID-B | ID-A
B1   | A1
B2   | A2

Table C
ID-C | ID-B | Value-C
C1   | B1   |   1
C2   | B2   |   1
C3   | B1   |   1

Table D
ID-D | ID-A  | Value-D
D1   | A1    | 1000
D2   | A1    | 500
D3   | A1    | 1000
D4   | A2    | 1000
D5   | A2    | 500

Table E
ID-E | ID-A  | Value-E
E1   | A1    | 2000
E2   | A1    | 1500
E3   | A2    | 500
E4   | A2    | 500
E5   | A2    | 1000

So, I want sum of value, following:

1. SUM(Value-C) of Table C AS tot_C for each ID-A of Table A.
2. SUM(Value-D) of Table D AS tot_D for each ID-A of Table A.
3. SUM(Value-E) of Table E AS tot_E for each ID-A of Table A.
4. tot_C+tot_D+tot_E AS tot_ALL for each ID-A of Table A.

I hope you can help me.
Thank you, Dear!

Upvotes: 1

Views: 64

Answers (2)

cn0047
cn0047

Reputation: 17091

select tb.id_A id_A, sum(valueC) tot_C 
from fiveTablesSum_C tc 
join fiveTablesSum_B tb on tc.id_B = tb.id_B 
group by id_A;
+------+-------+
| id_A | tot_C |
+------+-------+
| A1   |     2 |
| A2   |     1 |
+------+-------+

select id_A id_A, sum(valueD) tot_D from fiveTablesSum_D group by id_A;
+------+-------+
| id_A | tot_D |
+------+-------+
| A1   |  2500 |
| A2   |  1500 |
+------+-------+

select id_A id_A, sum(valueE) tot_E from fiveTablesSum_E group by id_A;
+------+-------+
| id_A | tot_E |
+------+-------+
| A1   |  3500 |
| A2   |  2000 |
+------+-------+

select
    t1.id_A,
    t2.tot_C, t3.tot_D, t4.tot_E,
    t2.tot_C + t3.tot_D + t4.tot_E tot_ALL
from fiveTablesSum_A t1
left join (
    select tb.id_A id_A, sum(valueC) tot_C
    from fiveTablesSum_C tc
    join fiveTablesSum_B tb on tc.id_B = tb.id_B
    group by id_A
) t2 on t1.id_A = t2.id_A
left join (
    select id_A id_A, sum(valueD) tot_D from fiveTablesSum_D group by id_A
) t3 on t1.id_A = t3.id_A
left join (
    select id_A id_A, sum(valueE) tot_E from fiveTablesSum_E group by id_A
) t4 on t1.id_A = t4.id_A
;
+------+-------+-------+-------+---------+
| id_A | tot_C | tot_D | tot_E | tot_ALL |
+------+-------+-------+-------+---------+
| A1   |     2 |  2500 |  3500 |    6002 |
| A2   |     1 |  1500 |  2000 |    3501 |
+------+-------+-------+-------+---------+

Upvotes: 1

Abdul Manaf
Abdul Manaf

Reputation: 4888

For your first query, try like below

SELECT 
    SUM(TableC.Value-C) as tot_C,TableC.ID-C
FROM
    TableC
        JOIN
    TableB ON TableB.ID-B = TableC.ID-B
        JOIN
    TableA ON TableA.ID-A = TableB.ID-A;

Similarly you can try other other queries as well , You should look for how to use JOINS

Upvotes: 0

Related Questions