Query Help

did you try this?

SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram;

That does not work. It returns "sProgram" but it returns Count(Students.sStudent) AS CountOfsStudent
data twice. I don't get Count(Students.sProgram) AS CountOfProgram.

I don't think I'm understanding your setup. Why are you joining on Data here?
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram;

it is not in the results or the group by.
What is the nature of what you want to show?
It seems that your first one shows Program + student count, e.g.
band 123
football 45
etc...

and the second one just counts the programs

am I on the right track? can you clarify a little bit?

Why not:
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students
GROUP BY Students.sProgram;

Sorry fanopoe, didn't see your response prior to mine.

Thanks for answering.

fanopoe - Yes, that is correct.

woodan1 - Your first one works but I don't really understand it. Your second one returns duplicate data the reverse of the fanopoe suggestion.

then if a result of the first one could be band 123, the count of the programs would be 1, because you have already grouped on that. So how do you envision the count of programs you are getting in your second query relating to band 123?

The first one is what is called a nested sql statement.  More or less just takes the two queries and combines them matching on sProgram.  Still bothers me that the second one doesn't do what we intended.

I don't see any need to use the Data table...To make the nested one smaller:
SELECT T1.sProgram, CountOfsStudent, CountOfProgram
FROM (
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students
GROUP BY Students.sProgram
) AS T1 INNER JOIN
(
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram
FROM Students
GROUP BY Students.sProgram
) AS T2 ON T1.sProgram=T2.sProgram

The data is from two tables...Students and Data.

The Students table has the sStudent and sProgram field. I need to know how many students are in each program.

The Data table has dStudent and a record each time they have activity and I need to know how many activities belong to each program.

Then I want that side-by-side in a query.

Again, this works fine...

SELECT T1.sProgram, CountOfsStudent, CountOfProgram
FROM (
SELECT Students.sProgram, Count(Students.sStudent) AS CountOfsStudent
FROM Students INNER JOIN Data ON Students.sStudent = Data.dStudent
GROUP BY Students.sProgram
) AS T1 INNER JOIN
(
SELECT Students.sProgram, Count(Students.sProgram) AS CountOfProgram
FROM Students
GROUP BY Students.sProgram
) AS T2 ON T1.sProgram=T2.sProgram

So if it's okay I guess I'll just use it.

Thanks,

xeb