Click here to Skip to main content
15,867,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 2 table who have 1 column name similar.
Now I want to calculate data and show in result table.

2 table as below

T1

liveid           point            userid
1                 2                10
2                 3                10


T2
postid           cdr              userid
    1            34                10
    2            23                10
    3            45                10
    4            24                20


Result Should be :
userid     sum           avg    Totalp    Totalreal
10          5             3      2         3
20          0             0      0         1


userid->distinct userid T2 ,Sum -> T1(point), avg -> (T1 point) , Totalp=count(userid or point) T1, Totalreal->count(T2.userid)

T1 userid cannot have value that doesn't in T2 userid


What I have tried:

I have tried below query :

SELECT t.userid,sum(r.point)'sum',ROUND(AVG(r.point))'avg',COUNT(r.point)'Totalp',COUNT(t.userid)'Totalreal' FROM T2 t LEFT JOIN T1 r USING (userid) GROUP BY t.userid


It show wrong data
userid     sum           avg    Totalp Totalreal
 10         15            3      6        6
 20         NULL          NULL   0        1
Posted
Updated 20-Nov-20 23:54pm
v7
Comments
Afzaal Ahmad Zeeshan 21-Nov-20 0:39am    
It shows the wrong data or does the query fail to execute?
Member 13067525 21-Nov-20 0:44am    
It show wrong data?
Member 13067525 21-Nov-20 0:55am    
i have updates my question
[no name] 21-Nov-20 5:06am    
You need to come up with better column names and some "logical explanation" for the point of all this. Otherwise, it just looks like you're farting around.
Member 13067525 21-Nov-20 5:56am    
I have updated again, let me know if it is understandable or not.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900