Re: SQL subquery question

I tried to simplify this by limiting the query to a single product. The first 5 columns of the query below return correct values but the last does not. The ReorderInitQty is being repeated for each occurance of an init_order_id.

SELECT InitAccts, InitQty, FillAccts, FillQty, ReorderAccts, ReorderInitQty
FROM (SELECT product_id, count(DISTINCT cust_id) InitAccts, sum(qty) InitQty

	FROM order_history
	WHERE order_type = 'I' and product_id = '355'
	GROUP BY product_id) i,


	(SELECT product_id, count(DISTINCT cust_id) FillAccts, sum(qty)


FillQty
	FROM order_history
	WHERE order_type = 'F' and product_id = '355'
	GROUP BY product_id) f,


	(SELECT i2.product_id, count(DISTINCT i2.cust_id) ReorderAccts,


sum(i2.qty) ReorderInitQty
	FROM order_history i2, order_history f2
	WHERE i2.product_id = f2.product_id and
		i2.product_id = '355' and 
		i2.cust_id = f2.cust_id and 
		i2.order_no = f2.init_order_id
	GROUP BY i2.product_id)

I need to fix the ReorderInitQty, expand the query to run for each product_id, and hopefully make it more efficient.

Any suggestions?
TIA! Received on Fri Apr 18 2003 - 17:32:49 CEST