Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 why get error in this select

Author  Topic 

sayer
Starting Member

35 Posts

Posted - 2013-04-16 : 14:46:40
error
Msg 8121, Level 16, State 1, Line 15
Column 'Course.HoursNum' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name) AS name,
COUNT(Student_Lectuer.FK_Student_ID)
* 100 / Course.HoursNum AS [numberOfAbsent], Course.Coures_ID, Section.Section_ID
FROM Course INNER JOIN
Lecture ON Course.Coures_ID = Lecture.FK_Course_ID INNER JOIN
Student_Courses ON Course.Coures_ID = Student_Courses.FK_Course_ID INNER JOIN
Student ON Student_Courses.FK_Student_ID = Student.ID_student INNER JOIN
Student_Lectuer ON Lecture.Lesson_Number = Student_Lectuer.FK_Lesson_Number AND Student.ID_student = Student_Lectuer.FK_Student_ID INNER JOIN
Teacher ON Lecture.FK_Teacher_ID = Teacher.Teacher_ID INNER JOIN
Teacher_Course ON Course.Coures_ID = Teacher_Course.FK_Course_ID AND Teacher.Teacher_ID = Teacher_Course.FK_Teacher_ID INNER JOIN
Section ON Teacher_Course.FK_Section_ID = Section.Section_ID
GROUP BY Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name), Course.Coures_ID, Section.Section_ID
,Student_Lectuer.FK_Student_ID
HAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1)
AND (COUNT(Student_Lectuer.FK_Student_ID) * 100 /Course.HoursNum= 102)

please help me

http://aman-services.net
for office
???? ???? ???????

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-16 : 18:17:27
In general, the rule is that when you have one or more aggregate function in the select clause or having clause, any column that is outside of the aggregate functions must be listed in the group by clause. I didn't quite understand the logic you are trying to implement, but one of these perhaps is required:
ELECT     Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name) AS name, 
COUNT(Student_Lectuer.FK_Student_ID)
* 100 / Course.HoursNum AS [numberOfAbsent], Course.Coures_ID, Section.Section_ID
FROM Course INNER JOIN
Lecture ON Course.Coures_ID = Lecture.FK_Course_ID INNER JOIN
Student_Courses ON Course.Coures_ID = Student_Courses.FK_Course_ID INNER JOIN
Student ON Student_Courses.FK_Student_ID = Student.ID_student INNER JOIN
Student_Lectuer ON Lecture.Lesson_Number = Student_Lectuer.FK_Lesson_Number AND Student.ID_student = Student_Lectuer.FK_Student_ID INNER JOIN
Teacher ON Lecture.FK_Teacher_ID = Teacher.Teacher_ID INNER JOIN
Teacher_Course ON Course.Coures_ID = Teacher_Course.FK_Course_ID AND Teacher.Teacher_ID = Teacher_Course.FK_Teacher_ID INNER JOIN
Section ON Teacher_Course.FK_Section_ID = Section.Section_ID
GROUP BY Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name), Course.Coures_ID, Section.Section_ID
,Course.HoursNum
,Student_Lectuer.FK_Student_ID
HAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1)
AND (COUNT(Student_Lectuer.FK_Student_ID) * 100 /Course.HoursNum= 102)


or perhaps

[code]SELECT Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name) AS name,
COUNT(Student_Lectuer.FK_Student_ID * 1.0/ Course.HoursNum)
* 100 AS [numberOfAbsent], Course.Coures_ID, Section.Section_ID
FROM Course INNER JOIN
Lecture ON Course.Coures_ID = Lecture.FK_Course_ID INNER JOIN
Student_Courses ON Course.Coures_ID = Student_Courses.FK_Course_ID INNER JOIN
Student ON Student_Courses.FK_Student_ID = Student.ID_student INNER JOIN
Student_Lectuer ON Lecture.Lesson_Number = Student_Lectuer.FK_Lesson_Number AND Student.ID_student = Student_Lectuer.FK_Student_ID INNER JOIN
Teacher ON Lecture.FK_Teacher_ID = Teacher.Teacher_ID INNER JOIN
Teacher_Course ON Course.Coures_ID = Teacher_Course.FK_Course_ID AND Teacher.Teacher_ID = Teacher_Course.FK_Teacher_ID INNER JOIN
Section ON Teacher_Course.FK_Section_ID = Section.Section_ID
GROUP BY Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name), Course.Coures_ID, Section.Section_ID
,Student_Lectuer.FK_Student_ID
HAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1)
AND (COUNT(Student_Lectuer.FK_Student_ID *1.0/ Course.HoursNum) * 100 = 102)
[/red]
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-17 : 00:31:23
i execute it but give me error
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.

thanks

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 00:51:11
whats the datatype of Course.HoursNum?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-17 : 09:57:17
Course.HoursNum int

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 10:01:00
Now that I am looking at it, the second query I posted does not really make sense. FK_Student_ID is probably a non-numeric column. Does the first query run at all, even if it is not giving you the right answers you want to get?
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-17 : 12:22:57
FK_Student_ID char



http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 12:55:14
Ok - can you try this?
SELECT Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name) AS name, 
COUNT(Student_Lectuer.FK_Student_ID) /SUM(Course.HoursNum)
* 100.0 AS [numberOfAbsent], Course.Coures_ID, Section.Section_ID
FROM Course INNER JOIN
Lecture ON Course.Coures_ID = Lecture.FK_Course_ID INNER JOIN
Student_Courses ON Course.Coures_ID = Student_Courses.FK_Course_ID INNER JOIN
Student ON Student_Courses.FK_Student_ID = Student.ID_student INNER JOIN
Student_Lectuer ON Lecture.Lesson_Number = Student_Lectuer.FK_Lesson_Number AND Student.ID_student = Student_Lectuer.FK_Student_ID INNER JOIN
Teacher ON Lecture.FK_Teacher_ID = Teacher.Teacher_ID INNER JOIN
Teacher_Course ON Course.Coures_ID = Teacher_Course.FK_Course_ID AND Teacher.Teacher_ID = Teacher_Course.FK_Teacher_ID INNER JOIN
Section ON Teacher_Course.FK_Section_ID = Section.Section_ID
GROUP BY Teacher.Teacher_ID, dbo.FUN_names(Student.frist_Name, Student.Second_Name, Student.Last_Name), Course.Coures_ID, Section.Section_ID
,Student_Lectuer.FK_Student_ID
HAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1)
AND (COUNT(Student_Lectuer.FK_Student_ID )/ SUM(Course.HoursNum) * 100.0 = 102)
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-17 : 15:44:14
no error but no result

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 15:51:17
Unfortunately, I am unable to offer any additional help because without having access to the tables and the data, it is hard to say why you don't get any results. Look at your HAVING clause to see if they are eliminating the rows. You can do this by removing the having clause altogether and seeing what you get. Then, see if there are any rows that would pass the filters in the having clause.
Go to Top of Page

sayer
Starting Member

35 Posts

Posted - 2013-04-17 : 16:02:26
thanks for your help .
i am very happy
how to make good ER diagram between tables
?

thanks....................................................................

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 16:18:28
SQL Server has a built-in diagram feature (in object explorer under the database name node). It is basic; there are commercial tools avilable as well. ErWin for example.
Go to Top of Page
   

- Advertisement -