MySQL getting count of a related table

Question

My SQL's more than a little rusty, and I'm having trouble getting this to work, so any assistance would be greatly appreciated.

I've got three tables:

sessions
---------------
id

session_visits
---------------
id | session_id

searches
---------------
id | session_visit_id

And I want to get a list of all sessions with the total visits and searches for each sessions, which is linked by the session_visits table. I can get the visits fine, but am having trouble getting the total of searches for each session too.

So far I've got

SELECT *,(SELECT Count(*)    
          FROM session_visits    
          WHERE session_id = sessions.id) AS num_visits, 
         (SELECT Count(*)    
          FROM searches    
          WHERE session_visit_id = (SELECT * FROM session_visits    
                                    WHERE session_id = sessions.id)) AS total_searches
FROM sessions

Which is failing on every count! Am I going about this the right way or am I fundamentally doing it wrong?

SELECT * FROM session_visits should be SELECT id FROM session_visits. And make sure, that query returns single row, otherway you will need to limit it (for example LIMIT1) — Uriil
– Uriil, Commented Jun 2, 2015 at 10:58
Rusty? If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Jun 2, 2015 at 11:16

StuartLC · Accepted Answer · 2015-06-02 11:24:43Z

2

You can do this in one query, by joining the 3 tables together, and then use aggregates COUNT DISTINCT (to eliminate duplicated) and COUNT to get the total number of rows for the child and grandchild rows respectively, grouped by the Sessionid.

SELECT s.id AS SessionId, COUNT(DISTINCT sv.id) AS SessionVisits, COUNT(sr.ID) AS Searches
FROM sessions s
  LEFT JOIN session_visits sv
  ON s.id = sv.session_id
  LEFT JOIN searches sr
  ON sr.session_visit_id = sv.id
GROUP BY s.id;

SqlFiddle here

(Edit : Changed to left outer joins to handle scenarios where there are no visits for session, or no searches per visit)

edited Jun 2, 2015 at 11:24

answered Jun 2, 2015 at 11:18

StuartLC

108k18 gold badges225 silver badges295 bronze badges

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

2 Comments

Rikkiebags Over a year ago

That's brilliant thanks - works exactly as I wanted. I was just about to tweak your statement to a LEFT JOIN but you beat me to it. Much obliged.

StuartLC Over a year ago

Cheers!. I'm guessing searches.id is a unique pk of sorts so no need for distinct, but obviously repeat the dose if you have more child tables to join in this manner.

Surekha · Accepted Answer · 2015-06-03 06:51:57Z

0

query generates error because of column name is not mentioned in query Operand should contain 1 column

and try with IN

edited Jun 3, 2015 at 6:51

answered Jun 2, 2015 at 11:55

Surekha

876 bronze badges

Collectives™ on Stack Overflow

MySQL getting count of a related table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related