CODEWITHSUNDEEP
mysqlsqlsum
Axtral
Axtral

Reputation: 3

MySql Sum with two tables

I have two tables

t1

id  Name    Total
1   Alex    100
2   Bob     100 
1   Alex    100

t2

id  Amount 
1   2   
1   3   
1   4   
2   12  
2   13

I need to get sum of Total and Amount. 

**select Name, sum(Total) as Total, sum(Amount) as Amount,day
from t1,t2
Where t1.id=t2.id
group by Name**

Result:

Alex 600    18 
Bob  200    25

Incorrect sum of Amount!

**select Name, sum(distinct Total) as Total, sum(Amount) as Amount,day
from t1,t2
Where t1.id=t2.id
group by Name**

Result:

Alex100 18

Bob 100 25

Incorrect sum of Amount.

MySql use distinct by value, i need distinct by id corect result that need be is

 Alex 200 18  
 Bob  100 25

How to get to this result?

Upvotes: 0

Views: 88

Answers (1)

juergen d
juergen d

Reputation: 204746

select t1.Name, 
       sum(t1.Total) as Total, 
       sum(t2.Amount) as Amount,
       day
from t1
left join 
(
  select id, sum(Amount) as Amount
  from t2
  group by id
) t2 on t1.id = t2.id
group by t1.Name

Upvotes: 1

Related Questions