Q:

Using GROUP BY SQL Function in a query with JOIN

I have this query which is working just fine:
SELECT [Exam Category]
      ,[Student No_]
      ,Gender
      ,Description
      ,Stage 
FROM [MYDB $Course Registration]
    inner join [MYDB $Student] 
        ON [Student No_] = No_
    inner join [MYDB $Programme]
        ON Code = Programme
where Gender != 0
order by [Exam Category] desc
        ,[Student No_]
        ,Stage

However, i would like to group by [Exam Category]:
SELECT [Exam Category]
      ,[Student No_]
      ,Gender
      ,Description
      ,Stage 
FROM [MYDB $Course Registration]
    inner join [MYDB $Customer] 
        ON [Student No_] = No_
    inner join [MYDB $Programme]
        ON Code = Programme
where Gender != 0
group by [Exam Category]
order by  Stage
         ,[Student No_]

but it brings up the error

Msg 8120, Level 16, State 1, Line 9 
  Column 'MYDB $Course Registration.Student No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

any help?

A:

If you want to aggregate by Exam Category, then I would expect a query like this:
SELECT [Exam Category], COUNT(*)
FROM [MYDB $Course Registration] inner join
     [MYDB $Customer]
    ON [Student No_] = No_ inner join
    [MYDB $Programme]
    ON Code = Programme
where Gender <> 0
Group by [Exam Category];

Note that all the other columns have been removed from the SELECT.
If this is not what you want, then you should edit your question and provide sample data and desired results.

