Re: SQL subquery question

From: Michael Draves <michael_draves_at_hotmail.com>
Date: 18 Apr 2003 13:14:18 -0700
Message-ID: <f28184a0.0304181214.2a5b82a3_at_posting.google.com>

I am assuming you have all the initial orders in the table and that there is only one initial order from a customer for a product.

select 	A.product_id,
	sum(decode(A.order_type,'I',1,0))   as INITIAL_CUSTOMERS,
	sum(decode(A.order_type,'I',nvl(A.qty,0),0)) as INITIAL_QUANTITY,
	sum(decode(A.order_type,'I',
		NVL(
			(SELECT COUNT(DISTINCT B.CUST_ID)
			 FROM yourtablename b
				WHERE A.PRODUCT_ID = B.PRODUCT_ID
                                AND   A.CUST_ID = B.CUST_ID
				AND   B.ORDER_TYPE = 'R'
			)
		,0)
	,0)) as REORDER_CUSTOMERS,
	sum(decode(A.order_type,'R',1,0)) as REORDER_ORDERS,
	sum(decode(A.order_type,'R',nvl(A.qty,0),0)) as REORDER_QUANTITY,
	sum(decode(A.order_type,'I',1 -
		NVL(
			(SELECT COUNT(DISTINCT C.CUST_ID)
			 FROM yourtablename C
				WHERE A.PRODUCT_ID = C.PRODUCT_ID
                                AND   A.CUST_ID = C.CUST_ID
				AND   C.ORDER_TYPE = 'R'
			)
		,0)
	,0)) as INITIAL_ORDER_ONLY

from yourtablename A
where A.order_type in ('I','R')
group by A.product_id

trentsams_at_email.com (Trent Sams) wrote in message news:<c19d9e50.0304171306.2b451e2_at_posting.google.com>...
> Hi,
>
> I'm trying to analyze the data in a table and the SQL to do this is
> way over my head. The table includes columns:
>
> cust_id
> order_type ('I' or 'R')
> order_date
> order_id
> init_order_id (null or an order_id)
> product_id
> qty
>
> The order_type column identifies if this is the initial order for this
> customer for this product or a reorder. If the order is a reorder
> then the init_order_id column is populated with the order_id of the
> initial order.
>
> Ultimately, I'd like to know by product_id
> count of customers have order_type = 'I', sum(qty)
> count of customers have order_type = 'R', sum(qty)
> count of customers have order_type = 'I' and no order_type = 'R',
> sum(qty)
>
> Can someone get me started in the right direction?
> TIA!!
Received on Fri Apr 18 2003 - 22:14:18 CEST