michaelw436_at_gmail.com wrote:
> On Aug 10, 11:56 am, DA Morgan <damor..._at_psoug.org> wrote:
>> michaelw436.s..._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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> I like the idea, but I still get the same results because when I sum
> the integer column that I create, I am still adding 1 for each day in
> the the 7 day range. I only want to count the distinct cust_id's
> inside of that 7 day range. Does that make sense?
Then add it after doing the grouping.
--
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 - 11:21:13 CDT
