ORACLE SQL Query - AVG

Question

So I have the following Oracle SQL Database table:

create table Customer
(customerID char(25) not null, 
name char(50), 
address char(100), 
dateRegistered DATE, 
noOfDvdHiring int, 
primary key (customerID));

And I have the following query: List the unique numbers and names of all customers who are hiring more DVDs than the average number of DVDs currently hired by all customers.

I'm sure this should involve a nested query but I am unsure where to start?

I have tried:

SELECT customerID, name
FROM Customer
WHERE noOfDvdHire > AVG(noOfDvdHire);

But receive this error message: ORA-00934: group function is not allowed here

Vamsi Prabhala · Accepted Answer · 2015-10-28T01:05:08.860

0

Try this

SELECT customerID, name
FROM Customer
WHERE noOfDvdHiring > (select AVG(noOfDvdHiring) from customer);

edited Oct 28 '15 at 01:05

answered Oct 28 '15 at 00:39

Vamsi Prabhala

48,685
4
36
58

Thank you, I thought that would work but I receive this: ORA-00904: "NOOFDVDHIRE": invalid identifier – Thomas Oct 28 '15 at 01:04
the actual column name is `noofdvdhiring`. changed it. – Vamsi Prabhala Oct 28 '15 at 01:05
If possible can you help with me another query I have? – Thomas Oct 28 '15 at 01:22
sure..but post it as a different question – Vamsi Prabhala Oct 28 '15 at 01:32
I have to wait 90 minutes I believe – Thomas Oct 28 '15 at 01:35
sure..then..what is your question? – Vamsi Prabhala Oct 28 '15 at 01:35
I have the following query: Find the unique numbers and names of all directors who have never directed a science fiction film. I have tried: SELECT DISTINCT directorName, Director.directorID FROM Director, Film WHERE Director.directorID = Film.directorID AND filmCategory <> 'science fiction'; But that just lists all of the Directors (I have tried != as well) – Thomas Oct 28 '15 at 01:41
try this. `SELECT d.directorName, d.directorID FROM Director d where not exists ( select 1 from Film f where d.directorID = f.directorID and f.filmCategory = 'science fiction')` – Vamsi Prabhala Oct 28 '15 at 01:48

score 0 · Answer 2 · answered Oct 28 '15 at 00:51

0

2 ways... the difference is in execution plans

select * from (
  select customer_id, noOfDvdHire, avg(noOfDvdHire) over () avgg from Customer
) where noOfDvdHire > avgg

select customer_id, noOfDvdHire from Customer
  where noOfDvdHire >  (select AVG(noOfDvdHire) from customer);

answered Oct 28 '15 at 00:51

are

2,535
2
22
27

Thank you, for both ways I get the following error: ORA-00904: "NOOFDVDHIRE": invalid identifier – Thomas Oct 28 '15 at 01:04

ORACLE SQL Query - AVG

2 Answers2