Re: Oracle SQL query help

Look slow , but it will work

> > select distinct(a.Name) "Product Name",
> > count(b.ProductEvent) "Units Sold"
> > from PHierarchyTbl a, PEventTbl b
> > where a.ProdID = b.ProdID(+)
> > and b.ProductEvent='Sale'
> > group by a.name

JOON "Dave Martin" <demonick_at_hotmail.com> wrote in message news:EuFq4.11209$Zp1.356200_at_newsread1.prod.itd.earthlink.net...
> The suggested query produces the error for the first line at a.name:
>
> ORA-00979: not a GROUP BY expression
>
> Thanks for the input though... still looking for a solution...
>
> <gmei_at_my-deja.com> wrote in message news:88ephg$k90$1_at_nnrp1.deja.com...
> > In article <vokq4.8704$Zp1.255298_at_newsread1.prod.itd.earthlink.net>,
> > "Dave Martin" <demonick_at_hotmail.com> wrote:
> > > Platform: Oracle 805 on Sun Solaris
> > >
> > > Items involved...
> > > 2 tables: PHierarchyTbl and SalesTbl (not my actual database)
> > >
> > > PHierarchyTbl (contains Product Hierarchy)
> > > ProdID PCategID Name ...
 (more
> > > columns)
> > > 10 Golf Equipment
> > > 234 10 Deluxe Golf Club Set
> > > 235 10 Standard Golf Tees
> > > 20 Tennis Equipment
> > > ...
> > >
> > > PEventTbl (contains Product Events: Sale, Return, Purchase, etc.)
> > > ProdID ProductEvent ...(more columns)
> > > 234 Sale
> > > 234 Sale
> > > 234 Return
> > > 235 Sale
> > > ...
> > >
> > > The inital query...
> > > select distinct(a.PCategID) "Product Category ID",
> > > count(b.ProductEvent) "Units Sold"
> > > from PHierarchyTbl a, PEventTbl b
> > > where a.ProdID = b.ProdID(+)
> > > and b.ProductEvent='Sale'
> > > group by a.PCategID
> > >
> > > Output...
> > > Product Category ID Units Sold
> > > 10 3
> > > 20 6
> > > ....
> > >
> > > The problem...
> > > The purpose of the above query (which works fine) is to
> > > get a summary of the items sold at the Category level.
> > > However I need the name to be on the output report,
> > > not the CategoryID.
> > >
> > > The Desired Result...
> > > Product Category ID Units Sold
> > > Golf Equipment 3
> > > Tenis Equipment 6
> > > ....
> > >
> > > Any suggestions will be much appreciated.
> > > Please copy me at dmarajh_at_yahoo.com.
> > > Thanks a bunch.
> > >
> > > Dave
> > >
> > >
> >
> > Try:
> >
> > select distinct(a.Name) "Product Name",
> > count(b.ProductEvent) "Units Sold"
> > from PHierarchyTbl a, PEventTbl b
> > where a.ProdID = b.ProdID(+)
> > and b.ProductEvent='Sale'
> > group by a.PCategID
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
Received on Thu Feb 17 2000 - 00:00:00 CST