2

I am doing some study for work on SQL queries.

I came across the following on TutorialsPoint and I'm confused as to why the answer is B and not C.

I have always been told that the GROUP BY Clause comes before HAVING and that HAVING always comes at the end. There is also a lot of documentation online to support my solution, for example: http://www.dba-oracle.com/t_oracle_group_by_having.htm and SQL query, sequence of execution

Can anyone tell me why B is correct in the following example and not C. The tutorial gives B as correct.

Consider the following schema-

STUDENTS(
    student_code, first_name, last_name, 
    email, phone_no, date_of_birth, 
    honours_subject, percentage_of_marks
);

Which of the following query will correctly list the average percentage of marks in each honours subject, when the average is more than 50 percent?

B.

select honours_subject,
    avg(percentage_of_marks)
from students
having avg(percentage_of_marks) > 50.0
group by honours_subject;

C.

select honours_subject,
    avg(percentage_of_marks)
from students
group by honours_subject
having avg(percentage_of_marks) > 50.0;
Community
  • 1
  • 1
Hairdo
  • 71
  • 7
  • 3
    `B` is not syntactically correct. Hence, it cannot be the correct answer. – Gordon Linoff Mar 21 '17 at 14:19
  • @GordonLinoff Oddly Oracle (at least 11.x) allows B to work. So the question is why is B more correct than C in this case... – xQbert Mar 21 '17 at 14:33
  • 1
    Oracle [10.2](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065706), [11.1](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2065777) and [12c](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2065777) documentation show the `GROUP BY` must precede `HAVING`. The implementation, however, does not seem to respect this and can have any ordering. Strangely, 12c appears to omit being able to have a `HAVING` clause without a `GROUP BY` clause - something that is valid in the earlier version's syntax diagrams. – MT0 Mar 21 '17 at 14:49
  • The tutorial is plain wrong. B is invalid standard SQL (although Oracle seems to allow it). Do you have a link to that tutorial? –  Mar 21 '17 at 14:51
  • 1
    We don't always need `group by`. This query works: `select avg(percentage_of_marks) from students having avg(percentage_of_marks) > 50` and it means: show me percentage only when percentage is greater than 50. – Ponder Stibbons Mar 21 '17 at 14:54
  • The questions keep etting jumbled around so I can't find it right now, but the link is here: https://www.tutorialspoint.com/sql/sql_online_quiz.htm – Hairdo Mar 21 '17 at 15:04
  • @Hairdo - You should look for a better resource. – Gurwinder Singh Mar 21 '17 at 15:12
  • Yes, I agree @GurV. I've changed to another tutorial. Thanks! – Hairdo Mar 21 '17 at 15:16

3 Answers3

3

Even though Oracle does allows B to work, it gives a wrong idea of what actually happens. Additionally, as pointed out by @a_horse_with_no_name, it is not valid as per SQL standard and may not work on other databases.

I think the C is more clear in terms of what is happening i.e. the having clause is evaluated after group by.

EDIT:

For those who think B doesn't work on Oracle, here is a demo:

Demo

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • 1
    I must admit I didn't think you could have the having before the group by. – xQbert Mar 21 '17 at 14:29
  • Thanks I was getting pretty confused with this one. Apologies for the styling issues. – Hairdo Mar 21 '17 at 14:40
  • @Hairdo - No problem. Glad to help out. The answer you accepted is wrong (at least as far as Oracle is concerned) – Gurwinder Singh Mar 21 '17 at 14:46
  • Additionally to the "more clear" part: B is not valid standard SQL, it will not work with e.g. Postgres. –  Mar 21 '17 at 14:49
  • @a_horse_with_no_name - Agreed. I've made the addition to the answer. – Gurwinder Singh Mar 21 '17 at 14:54
  • it does not even works on Oracle got an ORA-00979: not a GROUP BY expression error – Sandeep Mar 21 '17 at 15:25
  • @Sandeep - It does. You may be doing some mistake in your query. Added a demo for you in the answer. – Gurwinder Singh Mar 21 '17 at 15:29
  • @GurV Yes it does having prior to group by with subquery refactoring works but if I use a table and then insert those values it won't allow me to do the same. I tried comparing the explain plan for both query interchanging the position of having and group by, the explain is identical for both queries. Do you have any idea why it works with dual but not with table data? – Sandeep Mar 21 '17 at 15:42
  • @GurV asked question here because it would have helped everyone – Sandeep Mar 21 '17 at 16:00
1

Your tutorial is off.

HAVING qualifies GROUP BY. I suppose it's possible that there's some non-standard SQL parser out there that allows this syntax, but I don't really see why; I'm fairly certain Oracle would not support it (edit: apparently it does - weird). It'd be like allowing someone to put a WHERE clause before SELECT

Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • Yeah I know right? and I can't find a case where it matters on the results. So I can't see why B is better than C or vice-versa. – xQbert Mar 21 '17 at 14:32
1

B query is wrong.

The syntax of query always needs to follow WGHO

W- where clause

G- Group by

H- Having

O- order by

Where and Order by are optional and you can only use having if you use a group by statement

Sandeep
  • 774
  • 3
  • 8