CODEWITHSUNDEEP
sqloracle12c
brigfalk
brigfalk

Reputation: 13

SQL (Oracle 12c): How to select results from another select statement (nesting?/sub--query?)

I'm working on a homework problem for an intro SQL class that I'm taking. This week's topic is Summary Functions. Here is the problem:

Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.

Here bellow are the pictures of the relevant tables:

enter image description here

enter image description here

I've written this query which returns the average retail price by publisher name and category, but cannot figure out how to return just the average retail that is over $50.

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
group by p.name, b.category;

The above code returns the following:

enter image description here

Publisher Reed-N-Rite has an average retail below $50 - what needs to be added to my query so that I can filter the results from my statement to just those with retail > $50 ?

Upvotes: 0

Views: 197

Answers (2)

Hossein NooriAzad
Hossein NooriAzad

Reputation: 29

When you use aggregate function (like avg),you must use group by on other columns, And when you use group by ,you must use having for set where clause on aggregated column. Use Having for this:

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
group by p.name, b.category 
having avg(b.retail) > 50

Upvotes: 2

Olivier Depriester
Olivier Depriester

Reputation: 1625

Add an HAVING clause to your query. HAVING applies to the group functions:

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
group by p.name, b.category
having avg(b.retail) > 50;

Upvotes: 0

Related Questions