CODEWITHSUNDEEP
sqlsql-servergroup-byinner-join
Alfred Kimotho
Alfred Kimotho

Reputation: 101

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?

Upvotes: 0

Views: 457

Answers (2)

santho
santho

Reputation: 21

Note -- that if u r giving the group by option, then * cannot be in a select statement u have to mention the column names in select statement select columns should have aggregate function like max, count, sum etc, those columns which is not having the aggregate function name those columns name should be specified after group by clause

//in your case gender, description, stage is not included in group by,

![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]

reference of my code check it

select MAX(Employee_Details.NewEmployeeId)
    ,Employee_Details.First_Name
from Employee_Details
INNER JOIN EmployeeDesignation_List ON Employee_Details.Designation=EmployeeDesignation_List.Designation_id
WHERE Employee_Details.Status='Active'
GROUP BY Employee_Details.First_Name
ORDER BY Employee_Details.First_Name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 0

Related Questions