Tough Analytical SQL Question

From: <michaelw436.spam_at_gmail.com>
Date: Fri, 10 Aug 2007 15:03:21 -0000
Message-ID: <1186758201.120343.304280@q4g2000prc.googlegroups.com>

I am wondering if anyone can find a simple solution to this SQL query. The end of goal of the query is to give me a count of distinct cust_id for each ctype and order_date, for a set of ranges. For example, I want an example record in the end with

CTYPE = 'ECHO'

order_date = '09-aug-2007'
dist_1_day = 2
dist_7_day = 2

SELECT ctype_id ctype, order_date order_dt,
                       dist_1_day distinct_cust_1_day,
                       dist_7_day distinct_cust_7_day
                  FROM (SELECT DISTINCT order_date, ctype_id,
                                        COUNT (cust_id) OVER

(PARTITION BY order_date, ctype_id)  

dist_1_day,

                                        COUNT (cust_id) OVER

(PARTITION BY ctype_id ORDER BY order_date RANGE 7 PRECEDING)  

dist_7_day

                                   FROM (SELECT DISTINCT TRUNC
 
(order_date
                                                            )
order_date,
                                                         o.ctype_id,
                                                         o.cust_id
                                                    FROM orders.orders

o,  

webadmin.cust_details cd

                                                   WHERE o.order_date


>= to_date('01-aug-2007','DD-MON-YYYY') - 7

                                                     and ctype_id =
'ECHO'
                                                     AND order_type IN
('O')
                                                     AND ostatus <>
'X'
                                                     AND cd.cust_id =
o.cust_id
                                                     AND cd.is_guest =
0))
                 WHERE order_date >= to_date('01-aug-2007','DD-MON-

YYYY') The results of the inner query starting with "SELECT DISTINCT order_date" are below for your reference: ORDER_DATE CTYPE_ID CUST_ID

8/3/2007	ECHO	1720522
8/6/2007	ECHO	1720522
8/7/2007	ECHO	1720522
8/8/2007	ECHO	1720522
8/9/2007	ECHO	2475717
8/9/2007	ECHO	1720522

As you can see, there are only two distinct cust_IDs in the whole recordset, but when I run the whole query, it counts my dist_7_day = 6 because it is counting the number of rows, not the DISTINCT CUST_ID's. I tried to add the DISTINCT keyword to the COUNT (cust_id) OVER..., but that gives me a syntax error. I know there is something I am looking right over here that will solve this issue simply.

I am on 10gR2 enterprise. Thanks for looking! Received on Fri Aug 10 2007 - 10:03:21 CDT