michaelw436.spam_at_gmail.com wrote:
> 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!
Add the integer 1 to your inner query and then sum the integers.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Aug 10 2007 - 10:56:03 CDT
