OraFAQ Forum: SQL & PL/SQL » Top 20% of Customers

Home » SQL & PL/SQL » SQL & PL/SQL » Top 20% of Customers

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Top 20% of Customers [message #224537]

Wed, 14 March 2007 11:05

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

Report message to a moderator

Re: Top 20% of Customers [message #224555 is a reply to message #224537]

Wed, 14 March 2007 12:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Do a search for Top N and adapt it to percentage.

Report message to a moderator

Re: Top 20% of Customers [message #224641 is a reply to message #224537]

Wed, 14 March 2007 21:02

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

Maaher
Messages: 7065
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:15:51 CST 2024