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?
Received on Fri Aug 10 2007 - 11:07:20 CDT
