MySql Sum with two tables

Question

I have two tables

t1

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

t2

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?

sum(distinct Total) will sum just the distinct Total values. If two rows have the value 5, only one of them will be included in the SUM. This is not what you want, remove DISTIONCT. — jarlh
– jarlh, Commented Jan 30, 2015 at 9:01

juergen d · Accepted Answer · 2015-01-30 09:01:36Z

1

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

answered Jan 30, 2015 at 9:01

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql Sum with two tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related