Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Top 20%

Status
Not open for further replies.

areric

Programmer
Jul 13, 2003
47
US
Hey everyone,

Im trying to do some math in a query in order to limit my asp work.

What im trying to come up with is the top 20% of file owners based on a table that contains individual records of every file on a server.

THis table contains Owner (file owner), FileSize, and other records i dont need for this query.

I need for results any person who's sum(Filesize) is greater than 20% of the sum(all filesizes) grouped by owner. For them i need their total filesize, count of their files, and their owner name

I was trying this query but it wasnt working.

Select Count(*), Sum(FileSize) as Total, Owner,
completesum=(select Sum(FileSize) from FileScanner.dbo.FileInformation)
from FileScanner.dbo.FileInformation
where Total/completesum > .2
group by owner

Thanks
 
Try this:

Select Owner, Count(FileSize), Sum(FileSize) as Total,
completeSum,
Convert(float,Sum(FileSize)) / completesum AS FILE_PCT
from FileInformation
CROSS join (
select Sum(FileSize) As completeSum from
FileInformation) AS COMP
group by owner, completeSum
having Convert(float,Sum(FileSize)) / completesum > .2
 
Awesome, that worked perfectly thanks alot.
 
Nice trick with the cross join. I was working on a 3 statement version.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top