CODEWITHSUNDEEP
mysqlleft-join
Rachel Smiths
Rachel Smiths

Reputation: 155

LEFT JOIN 3 tables with GROUP BY and SUM

I have 3 tables: tblusers, tblproducts, and tbldailylog:

tblusers

UserID  UserName
------  --------
  1001  Mary
  1002  John

tblproducts

UserID  ProductKey  ProductName
------  ----------  ----------  
  1001  key1        p1
  1001  key2        p2

tbldailylog

UserID  ProductKey  Counter  Updated
------  ----------  -------  ----------
  1001  key1             10  2018-01-01
  1001  key1             15  2018-01-02
  1001  key2             50  2018-01-01

==========

Test1: LEFT JOIN 2 TABLES: tblusers and tblproducts

Query: SELECT tblusers.UserID, tblusers.UserName, tblproducts.ProductKey FROM tblusers LEFT JOIN tblproducts ON tblusers.UserID = tblproducts.UserID GROUP BY tblusers.UserID

Result: 

UserID  UserName  ProductKey
------  --------  ----------
  1001  Mary      key1
  1001  Mary      key2
  1002  John

==========

Test2: GET THE TOTAL COUNTER OF EACH ProductKey

Query: SELECT UserID, ProductKey, SUM(Counter) as Total FROM tbldailylog GROUP BY ProductKey

Result:

UserID  ProductKey  Total
------  ----------  -----
  1001  key1           25
  1001  key2           50

==========

I would like to have the below end result, how do I combine the 3 tables together? The end result is sorted by SUM(Counter) DESC. I apologize for couldn't display table data in a nice format for easier viewing. Thank you for your help.

End Result:

UserID  UserName  ProductKey  Total  ProductName
------  --------  ----------  -----  ----------
  1001  Mary      key2           50  p2
  1001  Mary      key1           25  p1
  1002  John

Edit: Sorry for not making it clear in my original posting. I added "ProductName" column to "tblproducts" and the final result, so we have to use all 3 tables in the query because "ProductName" doesn't exist in "tblusers" and "tbldailylog". Thanks.

Upvotes: 0

Views: 1278

Answers (3)

Jaydip Rakholiya
Jaydip Rakholiya

Reputation: 802

I think you can try this - 

select tu.userid,
       tu.UserName,
       tdl.ProductKey,
       tp.ProductName,
       sum(tdl.Counter) as Total
  from tblusers tu
  left join tbldailylog tdl
    on tu.userid = tdl.userid
  left join tblproducts tp
    on tu.userid = tp.userid
 group by tu.userid, tu.UserName, tdl.ProductKey, tp.ProductName
 order by sum(tdl.counter) desc

Upvotes: 2

Eric
Eric

Reputation: 3257

SELECT l.UserID, u.UserName, l.ProductKey, p.ProductName SUM(l.Counter) as Total 
FROM tblUsers u
LEFT JOIN tbldailylog l ON l.UserID = u.UserID
LEFT JOIN tblProducts p ON p.UserID = t.UserID AND p.ProductKey = t.ProductKey
GROUP BY l.UserID, l.ProductKey, u.UserName, p.ProductName

Upvotes: 0

urs_ng
urs_ng

Reputation: 33

If you are using Oracle database this will work : select t.UserID,t.UserName,tp.ProductKey,sum(tl.counter) total from TBLUSERS t left join TBLPRODUCTS tp on t.userid=tp.userid left join TBLDAILYLOG tl on t.userid = tl.userid and tp.productkey = tl.productkey group by tp.ProductKey,t.UserID,t.UserName order by total;

Upvotes: 0

Related Questions