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

---

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

---

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.

---

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)

---

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

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

---

---

quote:

---

Originally posted by hyderja

Thanks guys..

---