0

Question: A report listing all courses and their total number of attendances which have more than 5 attendances in each course by using aggregate function with GROUP BY and “HAVING” condition

Statement:

SELECT SUM(NO_OF_ATTENDANCES), COURSE_ID, EXERCISE_TYPE, COURSE_LOC
FROM I_COURSE
GROUP BY COURSE_LOC
HAVING SUM(NO_OF_ATTENDANCES) > 5;

Please help me and tell me what is wrong and why the error is obtained.

Table name is I_COURSE

Attributes are: COURSE_ID, EXERCISE_TYPE, COURSE_LOC, NO_OF_ATTENDANCES

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Oubaid Ghoorun
  • 3
  • 3
  • 1
    Are you using MySQL or Oracle DB ? – Madhur Bhaiya Oct 25 '18 at 18:42
  • Possible duplicate of [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) – OldProgrammer Oct 25 '18 at 18:44
  • All the non-aggregate columns in the select list probably need to be listed in the GROUP BY clause — at least, the retrograde versions of SQL I'm used to working with require that. – Jonathan Leffler Oct 25 '18 at 18:48
  • @JonathanLeffler - Actually, no. Did you read the question in detail? The non-aggregate columns in the select list, in this case, should be removed from the select list - not added to GROUP BY, which would result in an entirely different result. –  Oct 25 '18 at 20:00
  • @mathguy: I didn't read the question in detail, but it remains true that in classic SQL systems, the non-aggregate columns in the select-list should match the columns listed in the GROUP BY. You can reconcile the difference by adding columns to the GROUP BY (which is certainly what I envisaged, but there are issues with what the query is really supposed to find), _OR_ by removing columns from the select-list. Both techniques "work" (as in, produce executable SQL). I've not spent time studying the data and the desired results — neither of which is on display. There are reasons for commenting! – Jonathan Leffler Oct 25 '18 at 20:40

1 Answers1

0

You need just to group by COURSE_ID as :

SELECT SUM(NO_OF_ATTENDANCES), COURSE_ID
FROM I_COURSE
GROUP BY COURSE_ID
HAVING SUM(NO_OF_ATTENDANCES) > 5;

Firstly COURSE_ID column is enough for your desired result, and it's not possible to include [non-aggregated] columns in the SELECT list those are not in the GROUP BY list.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55