Problem with Count

[shauns1]

Hi

I'm getting confused with my joins in this query. I have a table holding:

viewID
SessionID
developmentID

There will be multiple viewID's for every developmentID and SessionID.

How can I count the number of different sessionID's for a specific DevelopmentID?

I am using the following, but it's giving me a list of sessionsID's with a count of viewsID's! In this particular case, I get 7 rows containing Session ID and ViewID's Count. But I need it to return 1 record which contains the Count of SessionID's. Which would be 7.

SELECT Count(s.SessionID)
FROM tblStatsSessions s, tblStatsPageViews p
WHERE p.SessionID = s.SessionID AND p.DevelopmentID=[@DevelopmentID]
Group By s.SessionID

Hope this all makes sense.

Thanks for your time.
Shaun

 

[Crowley16]

join on devID, and leave out the where statement

--------------------
Procrastinate Now!

 

[PHV]

A starting point:
SELECT p.DevelopmentID, Count(*) AS CountOfSession
FROM tblStatsSessions s INNER JOIN tblStatsPageViews p ON s.SessionID = p.SessionID
GROUP BY p.DevelopmentID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shauns1]

PHV - This now gives me a table of DevelopmentID's with ViewID's Count!

 

[PHV]

A starting point

 

[lespaul]

I am using the following, but it's giving me a list of sessionsID's with a count of viewsID's! In this particular case, I get 7 rows containing Session ID and ViewID's Count. But I need it to return 1 record which contains the Count of SessionID's. Which would be 7.

SELECT Count(s.SessionID)
FROM tblStatsSessions s, tblStatsPageViews p
WHERE p.SessionID = s.SessionID AND p.DevelopmentID=[@DevelopmentID]
Group By s.SessionID

when you add the GROUP BY s.SessionID, you tell the query to split out the results on each SessionID, the results are returning exactly what you asked for.