Re: Oracle SQL query help

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 Wed Feb 16 2000 - 00:00:00 CST