Author |
Topic |
sayer
Starting Member
35 Posts |
Posted - 2013-04-16 : 14:46:40
|
errorMsg 8121, Level 16, State 1, Line 15Column '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_IDFROM 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_IDGROUP 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_IDHAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1) AND (COUNT(Student_Lectuer.FK_Student_ID) * 100 /Course.HoursNum= 102) please help mehttp://aman-services.netfor 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_IDFROM 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_IDGROUP 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_IDHAVING (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_IDFROM 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_IDGROUP 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_IDHAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1) AND (COUNT(Student_Lectuer.FK_Student_ID *1.0/ Course.HoursNum) * 100 = 102) [/red] |
|
|
sayer
Starting Member
35 Posts |
Posted - 2013-04-17 : 00:31:23
|
i execute it but give me errorMsg 8115, Level 16, State 8, Line 1Arithmetic overflow error converting varchar to data type numeric.thankshttp://aman-services.netfor office???? ???? ??????? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-17 : 00:51:11
|
whats the datatype of Course.HoursNum?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sayer
Starting Member
35 Posts |
|
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? |
|
|
sayer
Starting Member
35 Posts |
|
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_IDFROM Course INNER JOINLecture ON Course.Coures_ID = Lecture.FK_Course_ID INNER JOINStudent_Courses ON Course.Coures_ID = Student_Courses.FK_Course_ID INNER JOINStudent ON Student_Courses.FK_Student_ID = Student.ID_student INNER JOINStudent_Lectuer ON Lecture.Lesson_Number = Student_Lectuer.FK_Lesson_Number AND Student.ID_student = Student_Lectuer.FK_Student_ID INNER JOINTeacher ON Lecture.FK_Teacher_ID = Teacher.Teacher_ID INNER JOINTeacher_Course ON Course.Coures_ID = Teacher_Course.FK_Course_ID AND Teacher.Teacher_ID = Teacher_Course.FK_Teacher_ID INNER JOINSection ON Teacher_Course.FK_Section_ID = Section.Section_IDGROUP 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_IDHAVING (Teacher.Teacher_ID = '29112') AND (Section.Section_ID = 1) AND (COUNT(Student_Lectuer.FK_Student_ID )/ SUM(Course.HoursNum) * 100.0 = 102) |
|
|
sayer
Starting Member
35 Posts |
|
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. |
|
|
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.netfor office???? ???? ??????? |
|
|
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. |
|
|
|