[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

top 10 PERCENT avg( CPS)

Posted on 2005-04-28
5
Medium Priority
?
778 Views
Last Modified: 2006-11-18

Following my query how can I do this...
This is what I have:
SELECT      top 10 PERCENT CPS
FROM          my table
my joins
WHERE                  my conditions
ORDER BY CPS DESC

This is what I want...>>>
SELECT      top 10 PERCENT avg( CPS)
FROM          my table
my joins
WHERE                  my conditions
ORDER BY CPS DESC
0
Comment
Question by:pinaldave
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1900 total points
ID: 13891524
AVG will give you just one value, so TOP 10 PERCENT is meaningless.  Unless you mean:

Select AVG(CPS)
From (Select TOP 10 PERCENT CPS
          From orders
          Order by CPS desc) a
0
 
LVL 21

Author Comment

by:pinaldave
ID: 13891689
I will answer you tomorrow after I go to office...
I think your suggestion looks good... that is what I may wanted...
Thank you more tomorrow.
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 100 total points
ID: 13891758
With the avg, you need to have group by such as

SELECT top 10 percent cps
  FROM (SELECT avg(cps) as cps
          FROM your_table
      GROUP BY department)

That is you will get the departments with  average CPS among their people in the top 10 percentile.
0
 
LVL 21

Author Comment

by:pinaldave
ID: 13895811
Thank you alll but somehow acperkins answer is what I was looking for. I might have not explained well what I may needed, but after running query and also verifing using good ol' calculator it brings correct answer.
Regards,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13896014
>>With the avg, you need to have group by such as<<
Nope.  No Group By required.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question