Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql query help
steve wrote...
> DA Morgan wrote...
>> steve wrote:
>>
>>> hi peeps
>>>
>>> i have a table set up
>>>
>>> table: orderitems
>>> orderid
>>> productid
>>> qty
>>>
>>> is there anyway of listing productid of items ordered x times?
>>>
>>> i have managed to get basic things working but found no reference to
this
>>> and not sure if the set up of my database would allow such a query
>>>
>>> cheers :)
>>>
>>> --
>>> get me tools and beer
>>
>> There is indeed.
>>
>> SELECT productid, COUNT(*)
>> FROM orderitems
>> GROUP BY productid
>> HAVING COUNT(*) = x;
>>
>> Go to http://tahiti.oracle.com and search on GROUP BY.
>>
>> Daniel Morgan
>
> Thanks very much.
>
> I didnt know it was possible, done a search on google gave me loads of
other
> stuff and I didnt have any idea how to word the search. Never occured to
me
> to check the oracle site!
>
> Thanks again :)
i've done some looking but am also having problems with another query, the same table:
orderid
productid
qty
say it has the data:
o1,p2,q1 o1,p5,q1 o2,p3,q1 o3,p2,q1 o3,p5,q1
how would i show the orderid or orders that contained p2 and p5
i have tried a mixture of group by, and sub queries and also looked into using 'having', i can display orders that have p2 or p5 but not orders that have both in them, taken me 2 hours of playing and looking around so far
-- get me tools and beerReceived on Wed Mar 05 2003 - 10:50:34 CST