Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

query help - aggregate function

Posted on 2007-03-28
4
Medium Priority
?
187 Views
Last Modified: 2010-03-19
Hi:

I have a table:
EmployeeID   Score
-------------   --------
1                  100
2                     40
3                     40

I want to find out the average score of all employees and the percentage of employees whose score is above average IN ONE QUERY. That is, in this case, the query should return 60 and 0.333. Anyway I can do this? Thanks.
0
Comment
Question by:sdc248
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 18811352
what about this

select  avg_score, ( select cast( count(*) as decimal (10,2)) from yourtable where score >= avg_score) / count_records
from (
select avg(score)  avg_score, count(*) count_records from yourtable
from yourtable
) as l
0
 

Author Comment

by:sdc248
ID: 18811535
Thanks angelIII. I think that'll do it. However, in reality the base table is in fact a result of a complicated sub-query, I don't feel like repeating it in the query. Is there any other way to do it?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18811579
with sql server 2005, you can use the CTE (common table expression).
with SQL server 2000 or lower, you could only store the results of the query into a table variable (or temp table), and continue processing from there.
0
 

Author Comment

by:sdc248
ID: 18812008
I use the CTE and it works. Thank you very muich.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

572 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