Home » SQL & PL/SQL » SQL & PL/SQL » Top 20% of Customers
Top 20% of Customers [message #224537] Wed, 14 March 2007 11:05 Go to next message
oraanu
Messages: 22
Registered: September 2005
Location: Boston
Junior Member
Hi All,

I have a table 'customer' with Cust_name,Address,Phone_num, total_scripts columns.I need to find the top 20% of total customers order by total_scripts DESC.
Can anyone help me how to do this.

Thanks in Advance
Anu
Re: Top 20% of Customers [message #224555 is a reply to message #224537] Wed, 14 March 2007 12:03 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Do a search for Top N and adapt it to percentage.
Re: Top 20% of Customers [message #224641 is a reply to message #224537] Wed, 14 March 2007 21:02 Go to previous messageGo to next message
jdufour
Messages: 4
Registered: March 2007
Junior Member
This should work.

SELECT * FROM (SELECT * FROM customer ORDER BY total_scripts DESC) 
WHERE ROWNUM < (SELECT floor(count(*) * .2)FROM customer);

[Updated on: Wed, 14 March 2007 21:04]

Report message to a moderator

Re: Top 20% of Customers [message #224688 is a reply to message #224641] Thu, 15 March 2007 02:51 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd have a look at the PERCENT_RANK() analytic function:
SQL> select last_name
  2       , salary
  3       , percent_rank() OVER ( ORDER BY salary         DESC
  4                                      , commission_pct DESC
  5                             ) pct
  6  from   employees
  7  where  department_id  > 80
  8  /

LAST_NAME                     SALARY        PCT
------------------------- ---------- ----------
King                           24000          0
Kochhar                        17000         .1
De Haan                        17000         .1
Greenberg                      12000         .3
Higgins                        12000         .3
Faviet                          9000         .5
Gietz                           8300         .6
Chen                            8200         .7
Urman                           7800         .8
Sciarra                         7700         .9
Popp                            6900          1

11 rows selected.

SQL> select last_name
  2       , salary
  3  from ( select last_name
  4              , salary
  5              , percent_rank() OVER ( ORDER BY salary         DESC
  6                                             , commission_pct DESC
  7                                    ) pct
  8         from   employees
  9         where  department_id  > 80
 10       )
 11  where pct <= .2
 12  /

LAST_NAME                     SALARY
------------------------- ----------
King                           24000
Kochhar                        17000
De Haan                        17000

SQL>


MHE
Previous Topic: Dumping a table from SAS to ORacle
Next Topic: How to check whether Varray is Null or Not.--V.Urgent
Goto Forum:
  


Current Time: Fri Dec 09 23:36:26 CST 2016

Total time taken to generate the page: 0.23522 seconds