CODEWITHSUNDEEP
mysqlsum
Ohidul Islam
Ohidul Islam

Reputation: 57

SUM from different column and from different table and show result in one row of each year

I have two tables named table1 and table2 . I've tried to sum some column but result is showing wrong

I've tried the following mysql query: 

SELECT t1.year
     , SUM(t1.deposit) TOTALDEPOSIT
     , SUM(t1.interest) TOTALINTEREST 
     , SUM(t1.otherinterest) TOTALOTHER
  FROM table1 t1
  LEFT 
  JOIN table2 t2
    ON t1.year = t2.year
 GROUP 
    BY t1.year

But result of SUM is not showing accurately

My tables are below

table1
| table1id| year| deposit| interest|
|---------|-----|--------|---------|
|        1|2019 |     20 |       1 |
|        2|2019 |     20 |       2 |
|        3|2019 |     20 |       1 |
|        3|2019 |     20 |       2 |
|        3|2020 |     20 |       3 |
|        3|2020 |     20 |       4 |

table2
| table2id| year | otherinterest|  
|----------------|--------------| 
|       1 | 2019 |        10    |       
|       2 | 2019 |        10    |

The expected result is

| YEAR | TOTALDEPOSIT| TOTALINTEREST  |TOTALOTHER |
|--------------------|----------------|-----------| 
| 2019 |       120   |        6       |     20    |
| 2020 |       40    |        7       |           |

But My query giving result

| YEAR | TOTALDEPOSIT| TOTALINTEREST  |TOTALOTHER |
|--------------------|----------------|-----------| 
| 2019 |       160   |        12      |     80    |
| 2020 |       40    |        7       |           |

So could you please anyone help me to solve this query?

Upvotes: 2

Views: 72

Answers (5)

Wojtek Piróg
Wojtek Piróg

Reputation: 31

Your query doesn’t work correctly because the intermediate result is probably not the same as you expected. Let’s try this query:

SELECT *
  FROM table1 t1
LEFT JOIN table2 t2
  ON t1.year = t2.year

Result will be:

+----------+------+---------+----------+----------+------+---------------+
| table1id | year | deposit | interest | table2id | year | otherinterest |
+----------+------+---------+----------+----------+------+---------------+
|        1 | 2019 |      20 |        1 |        1 | 2019 |            10 |
|        2 | 2019 |      20 |        2 |        1 | 2019 |            10 |
|        3 | 2019 |      20 |        1 |        1 | 2019 |            10 |
|        3 | 2019 |      20 |        2 |        1 | 2019 |            10 |
|        1 | 2019 |      20 |        1 |        2 | 2019 |            10 |
|        2 | 2019 |      20 |        2 |        2 | 2019 |            10 |
|        3 | 2019 |      20 |        1 |        2 | 2019 |            10 |
|        3 | 2019 |      20 |        2 |        2 | 2019 |            10 |
|        3 | 2020 |      20 |        3 |     NULL | NULL |          NULL |
|        3 | 2020 |      20 |        4 |     NULL | NULL |          NULL |
+----------+------+---------+----------+----------+------+---------------+

So we have 10 rows, not 6. You can see that for example sum of deposits for year 2019 is 160. Same number as in your "wrong" result.

This is because for each record in table1 where year is 2019 joining condition (t1.year = t2.year) is twice true. In other words for this rows from table1 where year equals 2019 we have two rows in result table - one with table2id=1 and antoher with table2id=2.

Upvotes: 3

Md Mobinur Rahman
Md Mobinur Rahman

Reputation: 395

Just use a simple subquery and it will works.

SELECT A.year, SUM(A.deposit) TOTALDEPOSIT, SUM(A.interest) TOTALINTEREST, 
    (SELECT SUM(B.otherinterest) FROM table2 B WHERE B.year= A.year) TOTALOTHER
FROM table1 A 
GROUP BY A.year

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17665

A sub query is a bit less wordy than a join.

drop table if exists t,t1;

create table t
(table1id int, year int, deposit int, interest int);
insert into t values
(        1,2019 ,     20 ,       1), 
(        2,2019 ,     20 ,       2), 
(        3,2019 ,     20 ,       1), 
(        3,2019 ,     20 ,       2), 
(        3,2020 ,     20 ,       3), 
(        3,2020 ,     20 ,       4); 

create table t1
( table2id int, year int, otherinterest int);  
insert into t1 values
(       1 , 2019 ,        10    ),       
(       2 , 2019 ,        10    );

select t.year,sum(deposit),sum(interest),
                (select sum(otherinterest) from t1 where t1.year = t.year) otherinterest
FROM t
group by t.year;



+------+--------------+---------------+---------------+
| year | sum(deposit) | sum(interest) | otherinterest |
+------+--------------+---------------+---------------+
| 2019 |           80 |             6 |            20 |
| 2020 |           40 |             7 |          NULL |
+------+--------------+---------------+---------------+
2 rows in set (0.00 sec)

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

Simple GROUP BY with LEFT JOIN is what you need, but order of that operations should be different from what you have :)

select t1.year, 
       t1.deposit totaldeposit, 
       t1.interest totalinterest, 
       t2.otherinterest * t1.cnt totalother
from (
    select year, sum(deposit) deposit, sum(interest) interest, count(*) cnt
    from table1
    group by year
) t1 left join (
    select year, sum(otherinterest) otherinterest
    from table2
    group by year
) t2 on t1.year = t2.year

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You should join the aggreated result from each table eg:

select  t1.year, tt1.totaldeposit, tt1.totalinterest, tt2.otherinterest
from   table1 t1
inner join  (
  select year, sum(deposit) totaldeposit, sum(interest) totalinterest
  from table1 
  group by year
) tt1 On t1.year = tt1.year
left join  (
   select year, sum(otherinterest) otherinterest
  from table1 
  group by year
) tt2 On t1.year = tt2.year

Upvotes: 0

Related Questions