Top 20%

[areric]

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

 

[TheDrider]

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

 

[areric]

Awesome, that worked perfectly thanks alot.

 

[donutman]

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]