MySql database selecting count(column) using inner joins

Question

I have three tables

USER :         idUser, Username
USERLOCATION : idUser, idLocation
SESSION :      idUser, idSession

What I want to find is all the users that are from a particular location and count how many sessions they have had. I'm nearly there with this SQL

    SELECT  u.idUser, u.Username, s.idSession
      FROM  rempad.User u
INNER JOIN  rempad.UserLocation l ON u.idUser = l.idUser
INNER JOIN  rempad.Session s ON u.idUser = s.idUser
     WHERE  l.idLocation = 12

This returns all the users belonging to a particular location and all the session ids. Where I am stuck is that I really want to be able able to count the the sessions for each user.

I've tried...

SELECT u.idUser, u.Username, COUNT(s.idSession) as SessionCount

but this returns only a single row and counts all the sessions in the session table rather than counting the sessions that belong to each individual user at that location.

Do I need to do a nested SELECT statement? I'm not really sure how to go about writing the query.

Any help much appreciated.

L

COUNT(blah) as something

Mihai
– Mihai

2013-09-02 18:38:55 +00:00
Commented Sep 2, 2013 at 18:38 — Mihai
– Mihai, Commented Sep 2, 2013 at 18:38

Rahul Tripathi · Accepted Answer · 2013-09-02 18:52:04Z

1

Try this:-

SELECT  u.idUser, u.Username, count(s.idSession) as x
      FROM  rempad.User u
INNER JOIN  rempad.UserLocation l ON u.idUser = l.idUser
INNER JOIN  rempad.Session s ON u.idUser = s.idUser
     WHERE  l.idLocation = 12
GROUP  BY u.iduser, 
      u.username

edited Sep 2, 2013 at 18:52

answered Sep 2, 2013 at 18:39

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Tripathi Over a year ago

@LindaKeating:- Try using the Group By clause then. Updated my answer!!

skv · Accepted Answer · 2013-09-02 18:51:18Z

1

I believe it would be better to use Group by along with the above

SELECT u.iduser, 
       u.username, 
       Count(s.idsession) AS x 
FROM   USER u 
       INNER JOIN userlocation l 
               ON u.iduser = l.iduser 
       INNER JOIN session s 
               ON u.iduser = s.iduser 
WHERE  l.idlocation = 12 
GROUP  BY u.iduser, 
          u.username

answered Sep 2, 2013 at 18:51

skv

1,8033 gold badges20 silver badges27 bronze badges

3 Comments

skv Over a year ago

sqlfiddle.com/#!2/8a29d/1/0 this is an example, seems to group by user

Linda Keating Over a year ago

Thankyou so much - I had made a mistake when using the group by - but tried it again and it worked. REally appreciate your help.

skv Over a year ago

Glad that I could help

Collectives™ on Stack Overflow

MySql database selecting count(column) using inner joins

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related