Vfrdtyky

Could you guys help me to make SELECT query for my simple case:

Table A:        

UserID  UserName

10      John

11      Mike

12      Matt





Table B:            

SessionID   UserID  SessionTime

124         10      20

123         10      122

42          10      30

324         11      55

534         11      42

344         12      19

653         12      32

I need this result:

Result Table:               

UserName    UserID  TotalTime

John        10      172

Mike        11      97

Matt        12      51

For one Table B this works:

SELECT UserID, SUM(SessionTime) AS "Total Time"

FROM TableB

GROUP BY UserID;

but I need to attach UserName to the final result

thank you

You can do that by using join and group by:

select a.UserId, a.UserName, sum(b.SessionTime) as TotalTime

from tableA a

left join tableB b on a.UserId = b.UserId

group by a.UserId, a.UserName;

Note: This would work for 1-to-many relations as in your case.

SELECT TableA.Username, TableA.User_ID, SUM(SessionTime) INNER JOIN

TableB ON TableA.User_ID = TableB.User_ID GROUP BY TableA.Username,

TableA.User_ID

SELECT a.UserName as "UserName" 

      ,a.UserID as "UserID"

      ,sum(b.SessionTime) as "TotalTime" 

FROM a LEFT JOIN b 

ON a.UserID = b.UserID GROUP BY a.UserID

Here. I used TABLE a and Table b