distinct group by join problem

Question

Here's what I want to achieve:

I have a number of categories, each one with products in it.

I want to produce a report that shows various information about those products for each category. So I have a query that looks something like:

select
category,
count(products),
sum(product_price),
from product
group by category 

So far so good.

But now I also want to get some category-specific information from a table that has information by category. So effectively I want to say:

join category_info on category

except that that will create a join for each row of each group, rather than just one join for each group.

What I really want to be able to say to sql is 'for each group, take the distinct category value, of which there's guaranteed to only be one since I'm grouping on it, and then use that to join to the category info table'

How can I accomplish this in SQL? By the way, I'm using Oracle 10g..

Many thanks!

Answer 1

select a.category, a.Count, a.SumPrice
    ci.OtherColumn
from (
    select p.category, 
        count(p.products) as Count, 
        sum(p.product_price) as SumPrice, 
    from product p
    group by category 
) a
inner join category_info ci on a.category = ci.category