count wirth zero count query

I

inungh

I would like to have a query to count students activities which
includes zero count like following

Student table

student ID, Student Name
1 John
2 Mary


Activity Table

Activity ID, Activity Name
1 Piano


Student Activity table

Student ID, Activity ID

1 1
1 1

I would like have a result like following

Strudent ID, Activity ID, Activity Count

1 1 2
2 2 0


For some reason my query does not show student 2, if here is no
activity for student 2.


Your help is great appreciated,
 
R

RonaldoOneNil

SELECT Student.[Student ID], [Student Activity].[Activity ID], Count([Student
Activity].[Activity ID]) AS [CountOfActivity ID]
FROM Student LEFT JOIN [Student Activity] ON Student.[Student ID] = [Student
Activity].[Student ID]
GROUP BY Student.[Student ID], [Student Activity].[Activity ID];
 
I

inungh

SELECT Student.[Student ID], [Student Activity].[Activity ID], Count([Student
Activity].[Activity ID]) AS [CountOfActivity ID]
FROM Student LEFT JOIN [Student Activity] ON Student.[Student ID] = [Student
Activity].[Student ID]
GROUP BY Student.[Student ID], [Student Activity].[Activity ID];



inungh said:
I would like to have a query to count students activities which
includes zero count like following
Student table
student ID, Student Name
     1              John
     2              Mary
Activity Table
  Activity ID, Activity Name
      1             Piano
Student Activity table
   Student ID,  Activity ID
       1                  1
       1                   1
I would like have  a result like following
  Strudent ID, Activity ID, Activity Count
      1                  1               2
      2                   2             0
For some reason my query does not show student 2, if here is no
activity for student 2.
Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks for the message,
For some reason, Access does not let me LEFT JOIN, Access let me do
RIGHT JOIN, but it does not give me right result.

I think that I need check my table links.

Thanks again,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top