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
 General SQL Server Forums
 New to SQL Server Programming
 Group by Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hyderja
Starting Member

5 Posts

Posted - 04/14/2013 :  23:22:15  Show Profile  Reply with Quote
Help I only have approx. 163 hairs left on my head


SELECT name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY exam_id
ORDER BY exam_id
;
getting "not a group by expression"

What I am doing wrong?

Edited by - hyderja on 04/14/2013 23:23:31

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 04/14/2013 :  23:56:55  Show Profile  Reply with Quote
Try these two queries.. Let us know which one is suitable for your data?
-- If you have unique exam name per exam_id
SELECT exam_id, name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY exam_id, name
ORDER BY exam_id
;

-- Another way in the case multiple exam names per exam_id
SELECT name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY name
ORDER BY exam_id
;

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/15/2013 :  02:13:11  Show Profile  Reply with Quote
USING is not a T-SQL statement.
are you using SQL Server?
ALso in any case grouping by examid will give you a row per exam which will defeat the whole purpose of grouping so i think what you may want is grouping on name


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

hyderja
Starting Member

5 Posts

Posted - 04/15/2013 :  08:21:10  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Try these two queries.. Let us know which one is suitable for your data?
-- If you have unique exam name per exam_id
SELECT exam_id, name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY exam_id, name
ORDER BY exam_id
;
This works but it adds a exam_id column which I do not want in my display.



-- Another way in the case multiple exam names per exam_id
SELECT name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY name
ORDER BY exam_id


This one I am still getting the

SQL Error: ORA-00979: not a GROUP BY expression




Go to Top of Page

hyderja
Starting Member

5 Posts

Posted - 04/15/2013 :  08:21:46  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

USING is not a T-SQL statement.
are you using SQL Server?
ALso in any case grouping by examid will give you a row per exam which will defeat the whole purpose of grouping so i think what you may want is grouping on name



I am using SQL developer.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3636 Posts

Posted - 04/15/2013 :  08:25:10  Show Profile  Reply with Quote
This forum is for Microsoft SQL Server. If you are using Oracle SQL Developer, you would get faster and better responses at an Oracle forum.
Go to Top of Page

hyderja
Starting Member

5 Posts

Posted - 04/15/2013 :  21:58:44  Show Profile  Reply with Quote
Next problem

How would this be created>


Create a view called current_enrollment. The view should display the number of students currently enrolled for each grade
level (in grade level order). (Note: the grade levels only range from 1–8)
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/15/2013 :  23:34:28  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by James K

This forum is for Microsoft SQL Server. If you are using Oracle SQL Developer, you would get faster and better responses at an Oracle forum.



^ This
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 04/16/2013 :  00:40:07  Show Profile  Reply with Quote
quote:
Originally posted by hyderja

Next problem

How would this be created>
Create a view called current_enrollment. The view should display the number of students currently enrolled for each grade
level (in grade level order). (Note: the grade levels only range from 1–8)


--This is in SQL Server...
CREATE VIEW current_enrollment
AS
 SELECT Grade_Level, COUNT(student_id) NoOfStudents
 FROM TableName
 WHERE Grade_Level BETWEEN 1 AND 8
 GROUP BY Grade_Level


Try to post Oracle questions in Oracle forums( that is the way to get quick responses from Oracle people)....
This is SQL Server Forum..

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 04/16/2013 :  00:41:30  Show Profile  Reply with Quote
quote:
Originally posted by hyderja

quote:
Originally posted by bandi

Try these two queries.. Let us know which one is suitable for your data?
-- If you have unique exam name per exam_id
SELECT exam_id, name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY exam_id, name
ORDER BY exam_id
;
This works but it adds a exam_id column which I do not want in my display.



-- Another way in the case multiple exam names per exam_id
SELECT name "Exam Name",
AVG(score) "Average Score",
COUNT (exam_id) "Number of Attempts"
FROM exam
JOIN student_exam
USING (exam_id)
WHERE test_date >= '01-JAN-2006'
GROUP BY name
ORDER BY exam_id


This one I am still getting the

SQL Error: ORA-00979: not a GROUP BY expression








--
Chandu
Go to Top of Page

hyderja
Starting Member

5 Posts

Posted - 04/16/2013 :  13:33:47  Show Profile  Reply with Quote
Thanks guys..
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 04/17/2013 :  01:32:57  Show Profile  Reply with Quote
quote:
Originally posted by hyderja

Thanks guys..


Welcome

--
Chandu
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.09 seconds. Powered By: Snitz Forums 2000