Using GROUP BY SQL Function in a query with JOIN

Question

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?

What do you want to achieve? You obviously do not know what grouping really means. — juergen d
– juergen d, Commented Jan 10, 2017 at 12:05
Exactly what the error message says! if you want any real help post some data, what the tables look like, and the expected result — e4c5
– e4c5, Commented Jan 10, 2017 at 12:08
The end goal is to create a report (retention summary) to find the retention rate for different programs, grouped by Exam category (ie Undergraduate, Postgraduate etc) and by Gender through the different Stages of their progression code SELECT COUNT (DISTINCT [Student No_]) FROM [MYDB $Course Registration] inner join [MYDB $Customer] ON [Student No_] = No_ inner join [MYDB $Programme] ON Code=Programme WHERE Semester = 'SEM1 16/17' AND Gender = '1' AND Programme = 'HT 100' AND Stage = 'Y2S1' code — Alfred Kimotho
– Alfred Kimotho, Commented Jan 10, 2017 at 12:09
This is what I've been using but it is a bit tedious since i have to go intake by intake then program by program then stage by stage — Alfred Kimotho
– Alfred Kimotho, Commented Jan 10, 2017 at 12:14

santho · Accepted Answer · 2017-01-10 13:03:25Z

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

Gordon Linoff · Accepted Answer · 2017-01-10 12:09:10Z

0

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.

answered Jan 10, 2017 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Using GROUP BY SQL Function in a query with JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related