CODEWITHSUNDEEP
mysqlsqldatabase-table
MikZ
MikZ

Reputation: 364

SQL GROUP BY with two tables involved

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

Upvotes: 0

Views: 53

Answers (3)

Shakil Hossain
Shakil Hossain

Reputation: 1743

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

Upvotes: 1

Bobby Bridgeman
Bobby Bridgeman

Reputation: 151

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

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23797

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.

Upvotes: 2

Related Questions