SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 why get error in this select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/16/2013 :  14:46:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/16/2013 :  18:17:27  Show Profile  Reply with Quote
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

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

Saudi Arabia
35 Posts

Posted - 04/17/2013 :  00:31:23  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/17/2013 :  00:51:11  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/17/2013 :  09:57:17  Show Profile  Reply with Quote
Course.HoursNum int

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

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/17/2013 :  10:01:00  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/17/2013 :  12:22:57  Show Profile  Reply with Quote
FK_Student_ID char



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

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/17/2013 :  12:55:14  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/17/2013 :  15:44:14  Show Profile  Reply with Quote
no error but no result

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

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/17/2013 :  15:51:17  Show Profile  Reply with Quote
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

Saudi Arabia
35 Posts

Posted - 04/17/2013 :  16:02:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/17/2013 :  16:18:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000