Posted by Adam Ruth on 10/11/06 04:07

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

• Next in forum: Re: DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'
• Prev in forum: SQL WHERE CLAUSE HELP
• Thread view: Re: SQL WHERE CLAUSE HELP

[Reply to this message]