SQL WHERE CLAUSE HELP

[RickyZane]

I need a little help with an assignment....
Basically I have a table with several PRODUCTS
PRODUCT(P_ID, P_Name, P_Class, P_Price, Product_Supplier)

Anyways....

I need to display:

List each Product Class, number of items in the class and the average
Price of the items in the class. List only those classes that have more
than three (3) items.

I have this

Select P_Class, COUNT(P_Class), AVG(P_Price)

>From PRODUCT

Group by P_Class;

Here are the results

P_CLASS COUNT(P_CLASS) AVG(P_Price)
------------ -------------- ------------
Camera 2 362.975
Printer 4 255.2375
DVD 2 83.75
Processor 3 139.95
Storage 8 391.855
Software 2 322.5
OS 2 292.495
Monitor 3 184.65

But I'm not for sure how to get the WHERE in there correctly to just
show me the items with the 3 or more COUNT (items) in them. Any help
would be great.

Thanks

[Adam Ruth]

RickyZane wrote:

I need a little help with an assignment....
Basically I have a table with several PRODUCTS
PRODUCT(P_ID, P_Name, P_Class, P_Price, Product_Supplier)

Anyways....

I need to display:

List each Product Class, number of items in the class and the average
Price of the items in the class. List only those classes that have more
than three (3) items.

I have this

Select P_Class, COUNT(P_Class), AVG(P_Price)

>>From PRODUCT

Group by P_Class;

Here are the results

P_CLASS COUNT(P_CLASS) AVG(P_Price)
------------ -------------- ------------
Camera 2 362.975
Printer 4 255.2375
DVD 2 83.75
Processor 3 139.95
Storage 8 391.855
Software 2 322.5
OS 2 292.495
Monitor 3 184.65

But I'm not for sure how to get the WHERE in there correctly to just
show me the items with the 3 or more COUNT (items) in them. Any help
would be great.

Thanks

What you need to use is the HAVING clause. WHERE operates on values
*before* any aggregates are applies, so any count(*) isn't yet
available. HAVING works on values *after* the aggregates. Example:

Select P_Class, COUNT(P_Class), AVG(P_Price)
From PRODUCT
Group by P_Class
HAVING COUNT(P_Class) >= 3

[DA Morgan]

Adam Ruth wrote:

RickyZane wrote:

>I need a little help with an assignment....

Select P_Class, COUNT(P_Class), AVG(P_Price)
From PRODUCT
Group by P_Class
HAVING COUNT(P_Class) >= 3

That's not assistance ... that is the answer. Please help
instructors help students by providing hints ... not answers.

A sufficient answer would have been: "Consider using the HAVING clause."

Thank you.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace x with u to respond)