Click here to Skip to main content
14,978,008 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


liveid           point            userid
1                 2                10
2                 3                10

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
Updated 20-Nov-20 23:54pm
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
Gerry Schmitz 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