Home » SQL & PL/SQL » SQL & PL/SQL » Help: Histogram using Width_Bucket Function (Oracle 9i R2, Sun Solaris)
Help: Histogram using Width_Bucket Function [message #317793] Sat, 03 May 2008 11:51 Go to next message
spsonkusare
Messages: 3
Registered: May 2008
Junior Member
Oracle Version: Oracle 9i Release 2
Operating system: Sun Solaris

I have table of item sales


(This is just an example set to describe my requirement)

Table sales
===============
Order_No char(10) not null,
Item_no char(10) not null,
Uom char(6),
No_of_Units number(12,4),
price number(12,4)
date_of_sale date


I want to generate histogram through a single query based on date bracketing (weekly)

SELECT COUNT(*) NO_IF_ITEMS, (PRICE * NO_OF_UNITS) VALUE,
WIDTH_BUCKET(DATE_OF_SALE, FROM_DATE, TO_DATE,
TO_NUMBER(TO_CHAR(TO_DATE,'DD-MON-YYYY') - TO_CHAR(FROM_DATE,'DD-MON-YYYY')) / 7) BUCKET_NO
FROM SALES
WHERE DATE_OF_SALE BETWEEN &FROM_DATE AND &TO_DATE

This query returns bucket_no for each record through which I can group it using other super-query

But this query distributes records in uneven ways.
For example, for period of 01-jan-2008 to 31-jan-2008 for from_date and to_date resp.,
it distributes first 8 days in bucket 1, 7 days in bucket 2, 8 days in bucket 3,
7 days in bucket 4 and last date ie. 31-jan-2008 in bucket 5.
This pattern continues for other intervals also.

I want distribution precisely in bucket of 7 days (or interval days i specify)
no matter what the data is or interval is.

How should i modify this query or what should i do to achieve desired result?
Re: Help: Histogram using Width_Bucket Function [message #317795 is a reply to message #317793] Sat, 03 May 2008 12:17 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
group by trunc((date_of_sale-&from_date)/7)

Regards
Michel
Previous Topic: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := .
Next Topic: Help in following Sql..
Goto Forum:
  


Current Time: Wed Dec 07 08:56:55 CST 2016

Total time taken to generate the page: 0.08702 seconds