[RESOLVED] Replicate results

[TheBigB]

The following query is to retrieve the sum of a table of orders per category.

The orderline table has the fields productnumber and numordered (which is the number of the same product ordered).
The product table has the fields productnumber, category and salesprice.
The query seems to work properly, but doesn't take numordered into account.

SELECT `product`.`category` , sum( `product`.`salesprice` ) AS 'total'
   FROM `product`
   WHERE `product`.`productnumber`
   IN (
      SELECT `orderline`.`productnumber`
      FROM `orderline`
   )
   GROUP BY `product`.`category`

I was thinking that maybe I could replicate the results to the number of numordered in the subquery.

Is that possible?
Or any other suggestions?
I can post the database if you need it.

Thanks

[si_the_geek]

This should work, but may not work as you want when there are no records for a product in the orderline table:

SELECT `product`.`category`, sum( `product`.`salesprice` * `orderline`.`numordered`) AS 'total'
FROM `product`
LEFT JOIN `orderline` ON (`orderline`.`productnumber`= `product`.`productnumber`)
GROUP BY `product`.`category`

[TheBigB]

Awesome, thanks
I had already figured out I had to use a left join, but just couldn't figure out how to multiply in sql.
As the frontend is PHP I got it working with the join only, but this makes a way better solution.